Re: SQL-Question: Error 393

>Date: Tue, 26 Aug 1997 12:12:06 +0200 >From: Richard Spitz <richard.spitz@ana.med.uni-muenchen.de> > >Jonathan Leffler wrote: >> SELECT team.druck, >> nverteil.folge, >> nverteil.tage, >> team.bezeich tbezeich, >> klinik.bezeich kbezeich, >> ort.bezeich obezeich, >> personal.vollname, >> team.zahl >> FROM team , OUTER nverteil, personal, OUTER klinik, ort >> WHERE team.druck BETWEEN 0 AND 249 >> AND team.teamnr = nverteil.teamnr >> AND nverteil.persnr = personal.persnr >> AND nverteil.teamnr = ort.teamnr[1] >> AND nverteil.teamnr = klinik.teamnr[1,3] >> AND nverteil.woche = "29.09.1997" >> ORDER BY druck, kbezeich, folge> >> The problem join is the one between nverteil.teamnr and klinik.teamnr. >> These tables are both tagged with the OUTER keyword and cannot therefore >> be joined to each other. >> You should probably use "team.teamnr = klinik.teamnr". > >Yes, that suggestion was correct! I did so and the query ran fine. Good. >But why does the join between "nverteil" and "ort" work? Because the optimizer is doing its best to do what you asked it to do, but you aren't quite sure what you've asked it for, and neither am I. See also below... >Isn't their relationship identical to "nverteil" and "klinik"? No, it isn't... There used to be an appendix on complex outer joins in the manuals, eg Appendix G in the 4.0 I4GL Reference Manual Vol 2. I have a nasty feeling that it simply isn't present in more recent editions of the manuals -- I couldn't immediately locate it in the 7.2 manual set, for instance. That's a pity as it is a good discussion. Doc - should this oversight be fixed? This appendix shows a diagrammatic way of looking at complex outer joins, meaning queries where there is more than one keyword OUTER. Using its diagramming, your query almost has the structure: team ---- personal ---- ort | | +------------+ | | nverteil klinik The top three tables are (or should be) joined as regular tables. You then have two independent outer joined tables, nverteil and klinik. Unfortunately, you have not specified any joins between the three inner joined tables, so you get a cartesian product of the rows in those tables. These are then selectively outer joined with nverteil and klinik... Your join conditions show that you would like to have: AND team.teamnr = nverteil.teamnr AND nverteil.persnr = personal.persnr AND nverteil.teamnr = ort.teamnr[1] AND nverteil.teamnr = klinik.teamnr[1,3] Apparently, you would like teams to appear regardless of whether there is matching data in the nverteil table, but you would like the other information to be inner joined to the nverteil table. You should probably write the FROM clause as: FROM team, OUTER (nverteil, personal, ort, klinik) -- Case 1 or, possibly, as: FROM team, OUTER (nverteil, personal, ort, OUTER klinik) -- Case 2 Assuming Case 1, this means that the nverteil, personal, ort and klinik records are joined using normal inner joins and the conditions specified on nverteil, but that the team table will be outer joined with the result of the inner join. In Case 2, there is an outer join between nverteil and klinik, but an inner join between nverteil and personal and ort, and the results of all this are outer joined with team. >I'll have to refine the query some more to get the desired results, >but that's a different problem... Yes, it is... Yours, Jonathan Leffler (johnl@informix.com) #include <witticism.h>