--- In firebird-support@yahoogroups.com, Milan Babuskov <milanb@...>
wrote:

> For example: looking for subject with default ORANGE color?
>
> select s.*
> from subject s
> left outer join sprop p on p.ref_id = s.id
> where p.type = 'COLOR' and (p.value = 'ORANGE' or p.value is null)
>
> Looking for subject with non-default GREEN color:
>
> select s.*
> from subject s
> join sprop p on p.ref_id = s.id
> where p.type = 'COLOR' and p.value = 'GREEN'
>

Unfortunately, I won't know when the criteria is on a default value or
a non-default value so I need a single query that will work in both
cases. Consider the case where the query on the user-defined property
is something like WHERE Prop.VAL LIKE "Ora%".

However, I think I am going to get rid of the phantom record idea for
the default value. Instead I am going to put it in the type table.
Something like:

TABLE StringType
ID LONG // type id
Name VARCHAR // name of type "Orange"
DefVal VARCHAR // default value

But then that still gives me the hideous query of:

SELECT *
FROM T
WHERE
(
T.ID NOT IN
(
SELECT StringVal.REF_ID
FROM StringVal
WHERE StringValue.TYPE_ID = COLOR
)
AND
EXISTS
(
SELECT *
FROM StringType
WHERE StringType.DEF_VAL LIKE "Ora%" AND StringType.ID = COLOR
)
)
OR
(
T.ID IN
(
SELECT StringVal.REF_ID
FROM StringVal
WHERE StringVal.VAL LIKE "Ora%" AND StringVal.TYPE_ID = COLOR
)
)

I'm sure there must be a simpler query here (more along the lines of
what you have proposed).


R.