Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!uunet!cti1!kmeek
From: kmeek@cti1.UUCP (Kevin Meek)
Newsgroups: comp.databases
Subject: Joining 3 tables in SQL
Keywords: SQL
Message-ID: <447@cti1.UUCP>
Date: 15 Feb 91 22:25:18 GMT
Organization: Comprehensive Technologies Int., Arlington VA
Lines: 86

I never did get SQL to do what I wanted.  This time I'll
try and be a little clearer and see if anyone can set me straight.

We have a table with account numbers and account information.

We have a table with planning data that is linked to the
account table via the account number.

We have a table with spending data that is linked to the account
table via the account number.

We want an output record for each account with the total 
planned amount and total expended amount.  It is easy to get
either one using sum and group by but getting both in one query
is more difficult.

A sketchy description of the tables I'm working with follows

Accounts table:
Account# [ string 10 ] ( primary key ),
name   [ string 10 ],
number [ string 10 ]
etc...


planning table:  ( NO PRIMARY KEY ) 
Account# [ string 10 ],
plan_amount [ huge amount ]
etc.  


spending table: ( no primary key )
Account# [ string 10 ],
exp_amount [ huge amount ]



there is a link index on plan account# references accounts(accounts#)
and a link index on exec account# references accounts(accounts#)


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

It is easy to get a query to give
100|widgets|600.00
OR
100|widgets|450.00

But I can't get both answers with one query.

I tried creating a view for each separately and then joining my views
but you can't use a where when your view has a group by clause. 

Anyone suggestions would be appreciated.

Thanks

Kevin
-- 
Kevin Meek 
kmeek@cti.com