Path: utzoo!utgpu!news-server.csri.toronto.edu!cs.utexas.edu!sdd.hp.com!wuarchive!rex!ukma!usenet.ins.cwru.edu!ncoast!allbery
From: allbery@NCoast.ORG (Brandon S. Allbery KB8JRR)
Newsgroups: comp.databases
Subject: Re: Joining 3 tables in SQL
Message-ID: <1991Feb21.004857.20940@NCoast.ORG>
Date: 21 Feb 91 00:48:57 GMT
References: <447@cti1.UUCP>
Reply-To: allbery@ncoast.ORG (Brandon S. Allbery KB8JRR)
Followup-To: comp.databases
Organization: North Coast Public Access Un*x (ncoast)
Lines: 52

As quoted from <447@cti1.UUCP> by kmeek@cti1.UUCP (Kevin Meek):
+---------------
| 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
+---------------

In general, you're out of luck.  I don't know if any of the SQLs out there
will do this, even with an intervening view.

In practice, this can be accomplished (I infer UNIFY 2000, that's what all
this sounds like (link indexes, etc.) using some external tools:

+---------------
| 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.
+---------------

So:  do query 1 into a temp file, query 2 into another temp file, then combine
them to get the composite (note that both should be sorted):

# the sed's will need to be changed if the first field is a string
# (this lets join consider fields 1 and 2 together to be the join field,
# since join rather stupidly only joins on one field)
SQL query1 | sed 's/|/,/' > /tmp/q1.$$
SQL query2 | sed 's/^|/,/' > /tmp/q2.$$
join -j 1 -o 1.1 1.2 2.2 -t'|' /tmp/q1.$$ /tmp/q2.$$ | sed 's/,/|/' |
    RPT reportscript -

I find this trick rather useful for dealing with things that SQL (both Unify's
SQL and SQL in general) can't handle.

++Brandon
-- 
Me: Brandon S. Allbery			    VHF/UHF: KB8JRR on 220, 2m, 440
Internet: allbery@NCoast.ORG		    Packet: KB8JRR @ WA8BXN
America OnLine: KB8JRR			    AMPR: KB8JRR.AmPR.ORG [44.70.4.88]
uunet!usenet.ins.cwru.edu!ncoast!allbery    Delphi: ALLBERY