Thread: Joining more than 2 tables

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

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

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