Left Join Complex Query

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

Responses

Browse pgsql-general by date

  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???