From: | Nils Zonneveld <nils(at)mbit(dot)nl> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Joining more than 2 tables |
Date: | 2001-04-29 06:37:44 |
Message-ID: | 3AEBB6AF.F38EBAEA@mbit.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Jeff Meeks wrote:
>
> Hi,
> I am trying to join 3 tables
> with this query
> select a.id, a.name, sum(b.qty), sum(c.qty)
> from a, b, c
> where a.id=xxx and b.id=a.id and c.id=a.id
>
> what the sums that get returned look as if they are a cross products of
> the b and c tables.
>
> What is the correct way to join these tables?
> Pointers to docs is welcome
>
> Thanks
> Jeff Meeks
> jmeekssr(at)net-serv(dot)com
>
I tried it with the folowing data:
speeltuin=# select * from a;
id | name
----+------
1 | Joe
2 | Pete
3 | John
(3 rows)
speeltuin=# select * from b;
id | qty
----+-----
1 | 1
1 | 2
2 | 2
2 | 3
(4 rows)
speeltuin=# select * from c;
id | qty
----+-----
2 | 4
2 | 5
3 | 7
(3 rows)
This statement gave the correct result for me:
select a.id, a.name,
(select sum(b.qty) from b where b.id = a.id) as b_qty,
(select sum(c.qty) from c where c.id = a.id) as c_qty
from a;
id | name | b_qty | c_qty
----+------+-------+-------
1 | Joe | 3 |
2 | Pete | 5 | 9
3 | John | | 7
(3 rows)
Maybe someone else has a more efficient one, but this one is correct.
Hope this helps,
Nils
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2001-04-29 08:14:50 | Re: Joining more than 2 tables |
Previous Message | will trillich | 2001-04-29 06:09:33 | Re: Re: crypt(table.field) ? |