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

Jeff Meeks writes:

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.

It's hard to tell what you want to happen, but perhaps you want two
separate queries:

select a.id, a.name, sum(b.qty) from a, b where a.id=xxx and b.id=a.id
group by a.id, a.name;

and the same with 'c' in place of 'b'.

--
Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter

What I am looking for is a query that will return a list of id's with a
sum from table b and a sum from table c like this:

id name sum(b) sum(a)
1 shell 34 50
2 jeff 40 20

Thanks
Jeff Meeks
jmeekssr@net-serv.com

P.S. Sorry for sending the reply to you directly Peter I wasn't paying
attention when I hit
the send key

Jeff Meeks writes:

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.

It's hard to tell what you want to happen, but perhaps you want two
separate queries:

select a.id, a.name, sum(b.qty) from a, b where a.id=xxx and b.id=a.id
group by a.id, a.name;

and the same with 'c' in place of 'b'.

--

On Wed, 2 May 2001, Jeff Meeks wrote:

What I am looking for is a query that will return a list of id's with a
sum from table b and a sum from table c like this:

id name sum(b) sum(a)
1 shell 34 50
2 jeff 40 20

Thanks
Jeff Meeks
jmeekssr@net-serv.com

SELECT id, name,
(SELECT sum(b) FROM b WHERE b.id=a.id) AS sum_b,
(SELECT sum(c) FROM c WHERE c.id=a.id) AS sum_c
FROM a;

--
Joel Burton <jburton@scw.org>
Director of Information Systems, Support Center of Washington