Path: utzoo!utgpu!news-server.csri.toronto.edu!bonnie.concordia.ca!thunder.mcrcim.mcgill.edu!snorkelwacker.mit.edu!apple!portal!fernwood!oracle!news
From: mwang@oracle.com (Michael Wang)
Newsgroups: comp.databases
Subject: Re: Joining 3 tables in SQL
Message-ID: <1991Feb19.222937.14037@oracle.com>
Date: 19 Feb 91 22:29:37 GMT
References: <447@cti1.UUCP>
Sender: news@oracle.com
Organization: Oracle Corporation, Redwood Shores, CA
Lines: 94

In article <447@cti1.UUCP> kmeek@cti1.UUCP (Kevin Meek) writes:
[...]
>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.
[...]
>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
[...]
>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.
[...]

One way to do this query is to create two views. One view would be

     SELECT account#, sum(plan_amount) sum_plan_amount
     FROM   planning
     GROUP BY account#

The other would be:

     SELECT account#, sum(exp_amount) sum_exp_amount
     FROM   spending
     GROUP BY account#

Your query would then be:

     SELECT accounts.account#, name, sum_plan_amount, sum_exp_amount
     FROM   accounts, planning_view, spending_view
     WHERE  accounts.account# = planning_view.account#
       AND  accounts.account# = spending_view.account#

It sounds like from your note that this is not going to work because your
version of SQL does not allow WHERE clauses with views that contain
GROUP BYs (which version are you using anyway?).

The other way to do this requires that you have a unique key column in both
of the tables, or the database you are working with has some column that
uniquely identifies every row in the database and is accessible to the user.
In ORACLE there is a ROWID column that does this. Using ROWID, your query
could be written as:

     SELECT accounts.account#, name,
            sum(planning.plan_amount)/count(distinct spending.rowid),
            sum(spending.exp_amount)/count(distinct planning.rowid)
     FROM   accounts, planning, spending
     WHERE  accounts.account# = planning.account#
       AND  accounts.account# = spending.account#
     GROUP BY accounts.account#, accounts.name

The query uses the fact that when you join tables, you are doing a cross
product, which means, given two tables A and B, every row in table A is
duplicated by the number of rows in table B. If you are doing a sum of a
column in table A while joining these two tables, then the result will
always be the result you would get without doing the join multiplied by the
number of rows in table B.

Again, this query depends on having some sort of unique key column for 
the tables you are joining. If the table doesn't have a unique key, then
you have to have some mechanism like ROWID to do this.


Hope this help,


Michael Wang
mwang@oracle.com