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]
