At 07:49 AM 2/08/2006, you wrote:

>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
> >

Three things you need to avoid here are redundancy, obligatory
relationships and NOT conditions. I suggest that you rethink your
tables and consider adding an intermediate table that links (is an
intersection of) Subject and Props. This table will have foreign
keys to the PKs of both tables. It will store records only for
attributes that exist for a Subject, and you can include a column
that indicates that a particular record should be used as the default
for that combination of Subject and Props. The Props table itself
will be small, since it will store one record for each possible property.

You can use a positive EXISTS() condition involving this intersection
table in several ways, e.g. in combination with a left join and or a
COALESCE or CASE condition, and more.

One strong recommendation I'd make to assist your thought processes:
train yourself to present your scratchpad examples in valid SQL
syntax, to lay out query source logically and to get past the belief
that SELECT * is of any benefit in sound design. Not many people are
born with an innate instinct for the logic of SQL, so attention to
such details is very beneficial to synaptic development for the
database programmer....

./heLen