List:General Discussion« Previous MessageNext Message »
From:Robert DiFalco Date:August 4 2006 11:11pm
Subject:RE: Query Help for Loosely Couple Properties
View as plain text  
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:jay@stripped] 
Sent: Wednesday, August 02, 2006 9:37 AM
To: Robert DiFalco
Cc: mysql@stripped
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";



Thread
Query Help for Loosely Couple PropertiesRobert DiFalco2 Aug
  • Re: Query Help for Loosely Couple PropertiesJay Pipes2 Aug
  • Re: Query Help for Loosely Couple PropertiesJay Pipes3 Aug
RE: Query Help for Loosely Couple PropertiesRobert DiFalco2 Aug
RE: Query Help for Loosely Couple PropertiesRobert DiFalco5 Aug
RE: Query Help for Loosely Couple PropertiesRobert DiFalco8 Aug