Path: utzoo!utgpu!news-server.csri.toronto.edu!bonnie.concordia.ca!thunder.mcrcim.mcgill.edu!snorkelwacker.mit.edu!spool.mu.edu!think.com!zaphod.mps.ohio-state.edu!ub!uhura.cc.rochester.edu!rochester!kodak!uupsi!sunic!fuug!demos!news-server
From: butenko@bob.srcc.msu.su (Vladimir A. Butenko)
Newsgroups: comp.databases
Subject: Re: joining 3 tables in SQL
Message-ID: <AE_V-ldatL@bob.srcc.msu.su>
Date: 18 Feb 91 14:43:11 GMT
Sender: news-server@jumbo.hq.demos.su
Reply-To: butenko@bob.srcc.msu.su
Organization: Gamma Software, Moscow State University
Lines: 100

In <447@cti1.UUCP> cti1!kmeek (Kevin Meek) writes:
>
>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 ]
>
It's a *very* common problem... I think that the only solution is:
SELECT Account#,name,
 SUM(SELECT plan_amount FROM planning_table WHERE Account# = Accounts.Account#),
 SUM(SELECT exp_amount  FROM spending_table WHERE Account# = Accounts.Account#),
FROM Accounts
 
 But:
 1) I'm not sure, that your SQL server can eat this expression
 2) It's rather slow, because tables plan_amount and exp_amount are scanned for
    each row in Accounts (of course, indexes are very important here).

 Maybe it's interesting for you how to write such a query in RQL:
 JOIN Accounts,
      plan :(GROUP planning_table BY Account# FOR Account#,S:SUM(plan_amount)),
      spend:(GROUP spending_table BY Account# FOR Account#,S:SUM(exp_amount )),
 WHERE Accounts.Account# = plan.Account# AND Accounts.Account# = spend.Account#
 FOR   Account#: Accounts.Account#, Name: Accounts.name,
       Plan:     plan.S           , Spend:spend.S

 I.e. you grouping both planning and spending tables by Account#, and then join
      them together and with Accounts table (I think that you tried to do it
      with SQL).

 Again, this query can be executed ineffectively by some stupid RQL server, so
 you can get your data by the single grouping operation:

  JOIN Accounts,
       plan_and_spend: 
         (GROUP (UNION
           (SELECT planning_table FOR Account#,plan:plan_amount,exp:0),
           (SELECT spending_table FOR Account#,plan:0,          exp:exp_amount)
                ) By Account# for plan:SUM(plan),exp:SUM(exp))
  WHERE Accounts.Account# = plan_and_select.Account#
  FOR   Account#: Accounts.Account#,     Name: Accounts.name,
        Plan:     plan_and_select.plan, Spend:plan_and_select:exp

Comments: we add an 'exp'  column to planning_table (with zero values),
             add an 'plan' column to spending_table (with zero values)
 (so these tables now have the same structure), glue them together with UNION 
 operation, then GROUP this table by Account#, calculating SUM(plan) and
 SUM(exp) and, finally, JOIN the result with the Accounts table to get "Names"


>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.
 
 I think that many SQL servers use smth like "macroprocessing" for views, i.e.
 they substitute the text of your view definition into your query and then try
 to interpret it. So, if you can't do something without views, creating some
 views can't help (just an opinion, maybe it's not right for some SQL servers).

>Anyone suggestions would be appreciated.
>Thanks
>Kevin

I hope this helps


-- 
Vladimir A. Butenko             Internet:       <Butenko@bob.srcc.msu.su>
Gamma Software,                 Phone:          7(095)939-2618
Moscow State University         Fax:            7(095)938-2136
* ===  To Gorby-lovers: communists can't reform, but they can pretend! === *