[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