Subject Re: [firebird-support] Loosely Coupled Properties with Implicit Defaults
Author Milan Babuskov
robert_difalco wrote:
> 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.

You don't have to.

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

For example: looking for subject with default ORANGE color?

select s.*
from subject s
left outer join sprop p on p.ref_id =
where p.type = 'COLOR' and (p.value = 'ORANGE' or p.value is null)

That query would list all subject that have ORANGE or don't have any

Looking for subject with non-default GREEN color:

select s.*
from subject s
join sprop p on p.ref_id =
where p.type = 'COLOR' and p.value = 'GREEN'

Milan Babuskov