So the question is what if I have multiple conditions say "COLOR LIKE
'Blue' [AND|OR] NAME LIKE 'Fred'". Will I have to create a JOIN for
each?
SELECT *
FROM T
LEFT JOIN StringVal CSV ON T.ID = CSV.REF_ID
INNER JOIN StringType CST
ON CSV.TYPE_ID = CST.ID AND CST.ID = COLOR
LEFT JOIN StringVal NSV ON T.ID = NSV.REF_ID
INNER JOIN StringType NST
ON NSV.TYPE_ID = NST.ID AND NST.ID = NAME
WHERE
( CSV.VAL = 'Blue' OR (CST.VAL = 'Blue' AND CSV.REF_ID IS NULL)
)
AND
( NSV.VAL = 'Fred' OR (NST.VAL = 'Fred' AND NSV.REF_ID IS NULL)
)
How do I generally simplify this?
R.
-----Original Message-----
From: Robert DiFalco [mailto:[EMAIL PROTECTED]
Sent: Friday, August 04, 2006 4:12 PM
To: [EMAIL PROTECTED]
Cc: mysql@lists.mysql.com
Subject: RE: Query Help for Loosely Couple Properties
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
