[DB-SIG] Help on this query

[Chris Cogdon]

On Tuesday 04 June 2002 18:57, Andy Todd wrote:

> Titu Kim wrote:
> > Hi there,
> >     I have two unrelated tables A and B which store
> > different data. Let say the followings are the table
> > schemas,
> >
> > Table A
> > ID  number,
> > Date number,
> > EarnA  number
> >
> > Table B
> > ID  number,
> > Date number,
> > EarnB number
> >
> > ID in Table A and Table B is the same data. Assume
> > Date in Table A and Table B is in number format. I can
> > get EarnA from Table A with this query
> >
> > Select sum(EarnA) from TableA
> > where ID=3DXX and Date between mmm and nnn;
> >
> > Similar for TableB i can get EarnB
> > Select sum(EarnA) from TableB
> > where ID=3DXX and Date between mmm and nnn;
> >
> > The number of rows in TableA and TableB are different
> > and EarnA and EarnB are two different data. Now if i
> > want to show EarnA and EarnB in one query for the same
> > ID in both Tables, how can i do this? Table schemeas
> > are simplified for discussion purposes.
> >
> > Thanks.
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Yahoo! - Official partner of 2002 FIFA World Cup
> > http://fifaworldcup.yahoo.com
> >
> >
> > _______________________________________________
> > DB-SIG maillist  -  DB-SIG@python.org
> > http://mail.python.org/mailman/listinfo/db-sig
>
> Try the following SQL statement;
>
> SELECT sum(A.EarnA), sum(B.EarnB)
> FROM   TableA A, TableB B
> WHERE  B.id =3D A.id
>
> This is bog standard SQL so I'd suggest you also take a look at a
> tutorial
> (http://directory.google.com/Top/Computers/Programming/Languages/SQL/FA=
Qs,_
>Help,_and_Tutorials/?tc=3D1)
>
> which may provide answers your questions more quickly than this mailing
> list ;-)

I don't believe that query will do what he was intending, since it create=
s a=20
cross-product over two tables, generating many MANY more rows than was=20
intended, inflating the 'earn' values or even decreasing them if the ID i=
n=20
one table is not reflected in the other table.

The right solution will depend on what you're trying to do. If you want a=
=20
single combined earn value for the summation from both tables, you can cr=
eate=20
a 'union' table of all the values, and then sum that. The following examp=
le=20
implies your table supports subselects:

select sum(earn) from ( select earna as earn from tablea where id=3Dxx an=
d=20
date>yy and date<zz union all select earnb as earn from tableb where id=3D=
xx=20
and date>yy and date<zz ) as joined;

However, if you want to keep the earn values from one table separate from=
 the=20
earn values from the other, you need a different query:

select sum(earna), sum(earnb) from ( select earna, null as earnb from tab=
lea=20
where id=3Dxx and date>yy and date <<zz union all select null as earna, e=
arnb=20
from tableb where id=3D2 and date>yy and date<zz ) as joined;

In both cases, you can replace 'joined' with any name. These tested to wo=
rk=20
under PostgreSQL. MySQL doesn't support subselects, so you'll have to mak=
e do=20
with either temporary tables, or two queries.

--=20
   ("`-/")_.-'"``-._        Chris Cogdon <chris@cogdon.org>
    . . `; -._    )-;-,_`)
   (v_,)'  _  )`-.\  ``-'
  _.- _..-_/ / ((.'
((,.-'   ((,/   fL