I think this only works if I "know" that "Orange" is the default value.
For example if "Orange" is the default and I am searching for "Blue" I
will have to use a different query. Since I don't know the default (its
in the type table) I would have to do two queries. How about something
like this?
SELECT *
FROM T
LEFT JOIN StringVal SV ON T.ID = SV.REF_ID
INNER JOIN StringType ST
ON SV.TYPE_ID = ST.ID AND ST.ID = COLOR
WHERE SV.VAL = "Blue" OR (ST.VAL = "Blue" AND SV.REF_ID IS NULL);
-----Original Message-----
From: Jay Pipes [mailto:[EMAIL PROTECTED]
Sent: Wednesday, August 02, 2006 9:37 AM
To: Robert DiFalco
Cc: mysql@lists.mysql.com
Subject: Re: Query Help for Loosely Couple Properties
On Tue, 2006-08-01 at 16:23 -0700, Robert DiFalco wrote:
> 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
> )
> )
SELECT * FROM T
LEFT JOIN StringVal V
ON T.ID = V.REF_ID
INNER JOIN StringType ST
ON V.TYPE_ID = ST.ID
AND ST.ID = COLOR
WHERE V.REF_ID IS NULL
OR V.VAL = "Orange";
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
