Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!sun-barr!apple!usc!elroy.jpl.nasa.gov!sdd.hp.com!hplabs!pyramid!infmx!dberg
From: dberg@informix.com (David I. Berg)
Newsgroups: comp.databases
Subject: Re: Joining 3 tables in SQL
Keywords: SQL
Message-ID: <1991Feb18.231620.19876@informix.com>
Date: 18 Feb 91 23:16:20 GMT
References: <447@cti1.UUCP>
Sender: news@informix.com (Usenet News)
Organization: Informix Software, Inc.
Lines: 82

In article <447@cti1.UUCP> kmeek@cti1.UUCP (Kevin Meek) writes:
> We have a table with account numbers and account information.
> .....
> A sketchy description of the tables I'm working with follows
> 
> Accounts table:
> Account# [ string 10 ] ( primary key ),
> name   [ string 10 ],
> number [ string 10 ]
> 
> planning table:  ( NO PRIMARY KEY ) 
> Account# [ string 10 ],
> plan_amount [ huge amount ]
> 
> spending table: ( no primary key )
> Account# [ string 10 ],
> exp_amount [ huge amount ]
> 
> .....
> if there are 3 planning records and 2 spending records for a given
> account say account #100
> 
> We would have
> 
> Accounts data as follows
> 
> 100|widgets
> 
> Planning records like this
> 
> 100|100.00|....
> 100|200.00|....
> 100|300.00|....
> 
> Spending records like this:
> 
> 100|150.00|...
> 100|250.00|...
> 
> I want a SQL select statement that will give me the following output
> 
> 100|widgets|600.00|400.00

How about the following:

select account.account#,sum(planning.plan_amount) plan_amount
  from account,planning
 where planning.account# = account.account#
 group by account#
  into temp t1;
select account.account#,sum(spending.exp_amount) exp_amount
  from account,spending
 where spending.account# = account.account#
 group by account#
  into temp t2;
select account#,plan_amount,exp_amount 
  from t1 outer t2;

(The outer join would be necessary only if not all accounts with planning
amounts have spending amounts.)

OR

create temp table t1 (
    account#,
    plan_amt,
    exp_amt);
insert into t1 (account#,plan_amt)
select account.account#,sum(planning.plan_amount)
  from account,planning
 where planning.account# = account.account#
 group by account#;
update t1 set (account#,exp_amount) =
(select account.account#,sum(spending.exp_amount)
 where spending.account# = account.account#
   and account.account# = t1.account#
 group by account#);
select * from t1;
  ___                   ___              dberg@cougar.informix.com
  /  ) __      . __/    /_ ) _       __  Informix Software Inc.  (303) 850-0210
_/__/ (_(_ (/ / (_(_  _/__> (-' -/~ (_-  5299 DTC Blvd #740; Englewood CO 80111
{uunet|pyramid}!infmx!dberg        The opinions expressed herein are mine alone.