From: | "Gregory Wood" <gregw(at)com-stock(dot)com> |
---|---|
To: | "PostgreSQL-General" <pgsql-general(at)postgresql(dot)org> |
Subject: | Left Join Complex Query |
Date: | 2001-02-02 16:49:36 |
Message-ID: | 004d01c08d38$2125e330$7889ffcc@comstock.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tony Grant | 2001-02-02 16:59:00 | Re: sintax??? |
Previous Message | Alfonso Peniche | 2001-02-02 16:43:44 | Re: sintax??? |