I was wondering if someone might be able to help me with a complex query. I
have two tables, ABC (columns A, B, and C) and BC (columns B and C) where
two columns from ABC (B and C) are foreign keys into BC. There can be
multiple A values for a given BC, or no values at all.
I want to do a query so that I get *all* the values from BC, and a true or
false value for whether A exists for a given value of BC. I've gotten close,
but I still have a problem. What I came up with was:
SELECT DISTINCT BC.B,BC.C,
CASE
WHEN ABC.A=1 THEN 'true' ELSE 'false'
END
FROM (BC LEFT JOIN ABC ON BC.B=ABC.B AND BC.C=ABC.C)
The problem is that although I do get true values if A exists for a given
value of BC, I also get false values. In other words, for every 1,2,true, I
also get a 1,2,false.
I can solve the problem programmatically, but I was hoping to create a view
to do all the work for me. I'd love any suggestions!
Greg