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.