Another options which is really just more of the same is to have a
DefaultValue for the properties and avoid the NO_ID record in the
property value table.

SELECT *
FROM Subject
WHERE
(
(Subject.ID NOT IN (SELECT * FROM StringVal))
AND
EXISTS(SELECT * FROM DefStringVal WHERE DefStringVal.VAL LIKE
"Orange") == 0)
)
OR
(
Subject.ID IN (SELECT * FROM StringVal WHERE StringVal.VAL LIKE
"Orange")
);

Still seems like there must be a better way....

R.

--- In firebird-support@yahoogroups.com, "robert_difalco"
<rdifalco@...> wrote:

>
> A have several tables I would like to be able to loosely associate
> properties. For simplicity lets say they are VARCHAR properties and
> that the user can define various types. The string_property table
> might look like this:
>
> REF_ID NUMERIC // F_KEY
> TYPE_ID NUMERIC
> VALUE VARCHAR
> Primary Key { REF_ID, TYPE_ID }
>
> A Property Type might be something like "COLOR" which has a default
> value of "Orange".
>
> So this is pretty simple. This string_property table has a row for
> each ID of the rows in the table it is being associated with. We'll
> call this the Subject table. For simplicity lets just say that every
> Subject row can have just one string_property row associated with it
> for any given TYPE.
>
> The problem is that there could be millions of rows in the Subject
> table and only some of them might have a COLOR property other than the
> default of Orange. I don't want to create a row in the string_property
> table for each Subject with the COLOR of Orange.
>
> So the question is this. How do I query for Subjects that have a
> Property that matches some arbitrary condition. The condition may or
> may not match the default property value.
>
> What I have come up with is pretty ugly, is there a better solution?
>
> My solution assumes that for every TYPE_ID there will be just one row
> written with the default value and whose REF_ID is a well-known ID
> that cannot exists in any Subject table. Let's assume this ID is 0.
>
> The query then looks something like this:
>
> SELECT *
> FROM Subject
> WHERE
> (
> (Subject.ID NOT IN (SELECT * FROM SPROP))
> AND
> ((SELECT REF_ID FROM SPROP WHERE VALUE LIKE "Orange") == 0)
> )
> OR
> (
> Subject.ID IN (SELECT * FROM SPROP WHERE VALUE LIKE "Orange")
> );
>
> The syntax may not be exactly correct, but would that be the correct
> logic? Is there another way I should be doing this (short of writing a
> record for each default that a Subject row implicitly has)?
>
> TIA
>