I have a table that contains properties that can be associated with any
table whose primary key is a LONG. Lets say that there is just one kind
of property. The table looks something like this:
TABLE StringVal
REF_ID BIGINT // row to associate property with
TYPE_ID BIGINT // type of string property
VAL VARCHAR // property value
P_KEY( REF_ID, TYPE_ID )
There is another table to represent a specific StringVal type along with
its default value:
TABLE StringType
ID BIGINT // The TYPE ID
NAME VARCHAR // The unique name of this property
DEF_VAL VARCHAR // The default value of this property
The rub is that the target table could have millions of records and I
only want a record in StringVal if the associated property is going to
be a value other than the default.
So consider that StringType has a record that defines a property named
"COLOR" with a default value of "ORANGE". For some table T, a T record
will only have a corresponding row in StringVal if it has a COLOR
property whose value has been explicitly set. It *could* be ORANGE but
in most cases it will be something else. Each row implicitly gets a
COLOR value of ORANGE.
The question is, how do I query this? Say I want all records from table
T whose COLOR property value is ORANGE.
The only thing I can come up with (and I'm no SQL expert and this looks
wrong to me) is the following:
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 "Orange" AND StringType.ID = COLOR
)
)
OR
(
T.ID IN
(
SELECT StringVal.REF_ID
FROM StringVal
WHERE StringVal.VAL LIKE "Orange" AND StringVal.TYPE_ID = COLOR
)
)
Any suggestions on how to simplify this (besides writing a row for each
T that has the default value)? Should I lay out the tables differently
or keep the DDL the same and just clean up the query?
R.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
