Обсуждение: 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

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

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

> Far simpler way to do this, without outer join:
>
> select bc.b,bc.c, (select count(*) from abc where bc.....)::boolean

You're absolutely right... I hate it when I make it too hard on myself!
Thanks for the replies!

Greg