Re: Left Join Complex Query

Far simpler way to do this, without outer join:

select bc.b,bc.c, (select count(*) from abc where bc.....)::boolean


On Fri, 2 Feb 2001, Gregory Wood wrote:

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