Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!sun-barr!apple!voder!pyramid!infmx!cortesi
From: cortesi@informix.com (David Cortesi)
Newsgroups: comp.databases
Subject: Re: joining 3 tables in SQL
Message-ID: <1991Feb22.164254.6490@informix.com>
Date: 22 Feb 91 16:42:54 GMT
References: <AE_V-ldatL@bob.srcc.msu.su> <1262@dkunix9.dk.oracle.com>
Sender: news@informix.com (Usenet News)
Organization: Informix Software, Inc.
Lines: 32

In article <1262@dkunix9.dk.oracle.com> bengsig@dk.oracle.com (Bjorn Engsig) writes:
] In <447@cti1.UUCP> cti1!kmeek (Kevin Meek) writes:
]
] |We want an output record for each account with the total 
] |planned amount and total expended amount.  [i.e. a join of two aggregates
] |and a table]
] |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.
] 
] Oracle will actually allow you to do that, e.g.
] 
] create view plansum(accno,ps) as select accno,sum(amount)
] from planned group by accno
] 
] create view expsum (accno,ex) as select accno,sum(amount)
] from expenses group by accno
] 
] select acc.accno, acc.accname, ... , expsum.ex, plansum.ps
] from acc, plansum, expsum
] where acc.accno = expsum.accno
] and   acc.accno = plansum.accno

Rather to to my astonishment, Informix SQL also permits this.  Curious
to know how the engine went about it, I used SET EXPLAIN ON, a feature
that makes the optimizer write its query plan to a file for reference.
It did about what you'd expect: executed each of the views in turn
with output to a temporary table, then joined the three tables.  It
picked one of the temp tables as the master table for the join; then
it built a temporary index on the other temp table so it could use
indexed lookups on both subordinate tables.  Which is just about the
sequence of operations one would enter manually to accomplish the same
result if the engine would not.