Ugh, in the last few queries the L1.ID = SV1.ID should actually be
L1.valueID = SV1.ID like so:

SELECT P.*
FROM Person P

LEFT JOIN StringLinks L1 ON P.ID = L1.parentID AND L1.refTypeID = 1
LEFT JOIN StringValue SV1 ON L1.valueID = SV1.ID
LEFT JOIN StringType ST1 ON ST1.ID = 1

LEFT JOIN StringLinks L2 ON P.ID = L2.parentID AND L2.refTypeID = 2
LEFT JOIN StringValue SV2 ON L2.valueID = SV2.ID
LEFT JOIN StringType ST2 ON ST2.ID = 2

WHERE ( SV1.f_val LIKE 'foo' OR
( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) )
AND
( SV2.f_val LIKE 'bar' OR
( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) )



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

>
> To support user defined properties I have the following tables:
>
> // defines a kind of value that can be set
> TABLE StringType
> LONG ID PKEY
> VARCHAR name
> ...
> VARCHAR defaultValue
>
> // the value of a defined value type (1..* to StringType)
> TABLE StringValue
> LONG parentID
> LONG typeID
> VARCHAR val
> UNIQUE INDEX ON ( parentID, typeID )
>
> Different string value types can be defined by giving them a new row in
> StringType. The StringValue.parentID can be a row from any table that
> has a LONG identity column (i.e. Person.ID). If a row in the parent
> table does not have a corresponding row in StringValue, the row
> implicitly has the default value.
>
> So to return all the Person rows that have either an explicit
> StringValue of ( type 1 and value foo ) or of ( type 2 and value bar ) I
> can just execute the following query.
>
> SELECT P.*
> FROM Person P
>
> LEFT JOIN StringValue SV1 ON P.ID = SV1.parentID AND SV1.refTypeID
> = 1
> LEFT JOIN StringType ST1 ON ST1.ID = 1
>
> LEFT JOIN StringValue SV2 ON P.ID = SV2.parentID AND SV2.refTypeID
> = 2
> LEFT JOIN StringType ST2 ON ST2.ID = 2
>
> WHERE ( SV1.f_val LIKE 'foo' OR
> ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) )
> AND
> ( SV2.f_val LIKE 'bar' OR
> ( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) )
>
> The above seems to work fine. I can also get rid of the two constant
> joins on the StringType table and just do a subqueries for their
> specified type id's default value, either approach works.
>
> Now, as Helen has suggested, I want to change things to introduce a
> ValueLink table so that if 100 rows have the same string value that I
> don't have to write the string 100 times. So my table structure changes
> like this:
>
> TABLE StringType
> LONG ID PKEY
> VARCHAR name
> ...
> VARCHAR defaultValue
>
> TABLE StringValue
> LONG ID PKEY // the value unique ID
> LONG typeID
> VARCHAR val
>
> TABLE StringLinks
> LONG parentID PKEY
> LONG valueID PKEY
>
> My naive (and incorrect) approach to the query was this:
>
> SELECT P.*
> FROM Person P
> LEFT JOIN StringLinks L ON P.ID = L.parentID
>
> LEFT JOIN StringValue SV1 ON L.ID = SV1.ID AND SV1.refTypeID = 1
> LEFT JOIN StringType ST1 ON ST1.ID = 1
>
> LEFT JOIN StringValue SV2 ON L.ID = SV2.ID AND SV2.refTypeID = 2
> LEFT JOIN StringType ST2 ON ST2.ID = 2
>
> WHERE ( SV1.f_val LIKE 'foo' OR
> ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) )
> AND
> ( SV2.f_val LIKE 'bar' OR
> ( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) )
>
> As you can probably tell this wont work because the two values I am
> searching for will now show up in two different rows (rather than joined
> columns of the same row) so the AND of the search terms will always
> fail.
>
> Can anyone think of a clean way to do this or do I have to get rid of
> the joins altogether and do a subquery for each StringValue search term?
>
> What is below works but does not seem like a useable option:
>
> SELECT DISTINCT P.*
> FROM Person P
>
> LEFT JOIN StringLinks L1 ON P.ID = L1.parentID
> LEFT JOIN StringValue SV1 ON L1.ID = SV1.ID AND SV1.refTypeID = 1
> LEFT JOIN StringType ST1 ON ST1.ID = 1
>
> LEFT JOIN StringLinks L2 ON P.ID = L2.parentID
> LEFT JOIN StringValue SV2 ON L2.ID = SV2.ID AND SV2.refTypeID = 2
> LEFT JOIN StringType ST2 ON ST2.ID = 2
>
> WHERE ( SV1.f_val LIKE 'foo' OR
> ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) )
> AND
> ( SV2.f_val LIKE 'bar' OR
> ( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) )
>
>
> The other thing I can do (which is not as normalized) is to simply take
> the refTypeID out of the StringValue table and add it as a third column
> to the StringLinks table. Then I can scope the two joins by the typeID
> and get rid of the duplicate rows without a distinct. But it doesn't
> good right either.
>
> SELECT P.*
> FROM Person P
>
> LEFT JOIN StringLinks L1 ON P.ID = L1.parentID AND L1.refTypeID = 1
> LEFT JOIN StringValue SV1 ON L1.ID = SV1.ID
> LEFT JOIN StringType ST1 ON ST1.ID = 1
>
> LEFT JOIN StringLinks L2 ON P.ID = L2.parentID AND L2.refTypeID = 2
> LEFT JOIN StringValue SV2 ON L2.ID = SV2.ID
> LEFT JOIN StringType ST2 ON ST2.ID = 2
>
> WHERE ( SV1.f_val LIKE 'foo' OR
> ( SV1.val IS NULL AND ST1.defaultVal LIKE 'foo' ) )
> AND
> ( SV2.f_val LIKE 'bar' OR
> ( SV2.val IS NULL AND ST2.defaultVal LIKE 'bar' ) )
>
>
> TIA,
>
> R.
>