[GENERAL] Re: Joining more than 2 tables

[Nils Zonneveld]

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
 [EMAIL PROTECTED]
 

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

---(end of broadcast)---
TIP 6: Have you searched our list archives?

http://www.postgresql.org/search.mpl

[GENERAL] RE: Joining more than 2 tables

[Joel Burton]

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  namesum(b)  sum(a)
   1   shell34  50
   2   jeff 40  20
 
 Thanks
 Jeff Meeks
 [EMAIL PROTECTED]

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   [EMAIL PROTECTED]
Director of Information Systems, Support Center of Washington


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html