How about:
CREATE VIEW foo AS
SELECT BC.B, BC.C, ABC.A
WHERE ABC.B = BC.B AND ABC.C = BC.C
UNION
SELECT BC.B, BC.C, 0
WHERE NOT EXISTS (
SELECT ABC.A FROM ABC
WHERE ABC.B = BC.B AND ABC.C = BC.C
);
I think the new beta code also contains outer join support as well, so if
you use that you could write the above using LEFT OUTER JOIN syntax. There
has been problems in the past with UNION's, DISTINCT's and VIEW's as a
combination before so YMMV based upon your version of PostgreSQL.
Hope that helps,
Mike Mascari
mascarm@mascari.com
-----Original Message-----
From: Gregory Wood [SMTP:gregw@com-stock.com]
Sent: Friday, February 02, 2001 11:50 AM
To: PostgreSQL-General
Subject: [GENERAL] Left Join Complex Query
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