Re: Joining more than 2 tables

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@net-serv.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