Multi-Level Outer Joins - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Multi-Level Outer Joins

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-07 : 11:03:41

Kevin writes "To start off with, this is a problem that arose from using Crystal Reports. We had to link one table to 3 tables (using left outer joins) and then we had to have one of those three tables linked to another table (this should be a left outer join too but we can use a normal inner join if need be). The problem we have is that we can modify the SQL statement that Crystal Reports generates, but it has to be one statement. Is there any way of nesting joins in outer joins? I've tried subqueries and some other odd combinations of joins and subqueries but nothing seems to work. Here is the original statement generated by Crystal Reports:

SELECT
Log.IDNUMBER, Log.DATE, Log.TIME, Log.ENTRYDATE, Log.ENTRYTIME, Log.DESCRIPTION, Log.TYPE, Log.STATION, Log.SHIFT,
Incident.INCIDENTNUMBER, Incident.EXPOSURENUMBER, Incident.TYPESITUATIONFOUNDTEXT,
Classes.CLASSNAME, Classes.LEADINSTRUCTORLOOKUPID, Classes.LEADINSTRUCTORNAME,
Company.TYPEOFCOMPANYREPORT, Company.DATEOFACTIVITY, Company.TIMEOFACTIVITY, Company.NOTES,
IncdPers.PERSONLOOKUPID, IncdPers.PERSONNAME, IncdPers.TIMEATACTIVITY, IncdPers.TIMEPAID, IncdPers.ACTIVITY
FROM
Log Log,
Incident Incident,
classes Classes,
Company Company,
IncdPers IncdPers
WHERE
Log.IDNUMBER *= Incident.DAILYLOGIDNUMBER AND
Log.IDNUMBER *= Classes.DAILYLOGIDNUMBER AND
Log.IDNUMBER *= Company.DAILYLOGIDNUMBER AND
Company.IDNUMBER = IncdPers.REPORTIDNUMBER AND
Company.TYPEOFACTIVITY = IncdPers.TYPEOFACTIVITY
ORDER BY
Log.DATE ASC,
Log.TIME ASC,
Log.IDNUMBER ASC

Any help would be appreciated. We are trying to prevent using stored procedures with this since we allow the end user to modify the reports and they are fairly non-technical and stored procedures are fairly complicated. Plus we have only two developers and 300+ reports (100+ of these will probably fall into this category, so 100+ stored procedures to run the reports)"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-05-07 : 12:43:40

You can do this using the ANSI join syntax:

SELECT
Log.IDNUMBER, Log.DATE, Log.TIME, Log.ENTRYDATE, Log.ENTRYTIME, Log.DESCRIPTION,
Log.TYPE, Log.STATION, Log.SHIFT, Incident.INCIDENTNUMBER, Incident.EXPOSURENUMBER,
Incident.TYPESITUATIONFOUNDTEXT, Classes.CLASSNAME, Classes.LEADINSTRUCTORLOOKUPID,
Classes.LEADINSTRUCTORNAME, Company.TYPEOFCOMPANYREPORT, Company.DATEOFACTIVITY,
Company.TIMEOFACTIVITY, Company.NOTES, IncdPers.PERSONLOOKUPID, IncdPers.PERSONNAME,
IncdPers.TIMEATACTIVITY, IncdPers.TIMEPAID, IncdPers.ACTIVITY
FROM
Log LEFT JOIN Incident ON Log.IDNUMBER=Incident.DAILYLOGIDNUMBER
LEFT JOIN Classes ON Log.IDNUMBER=Classes.DAILYLOGIDNUMBER
LEFT JOIN Company ON Log.IDNUMBER=Company.DAILYLOGIDNUMBER
INNER JOIN IncdPers IncdPers ON Company.IDNUMBER=IncdPers.REPORTIDNUMBER
AND Company.TYPEOFACTIVITY=IncdPers.TYPEOFACTIVITY
ORDER BY
Log.DATE ASC,
Log.TIME ASC,
Log.IDNUMBER ASC

The *= has been marked as obsolete for the last 3 versions of SQL Server (since 6.5) and should no longer be used. It would be a very good idea for you to review ALL of your queries and change them to use the ANSI LEFT/RIGHT/INNER JOIN syntax as soon as you can. Not only is it standard, and not only will it continue to be supported, but it is also logically correct, where the *= may cause ambiguous results because of the way it needs to be processed.

SQL Server Books Online has more detail on the JOIN syntax (and also the *= operator issues)

Edited by - robvolk on 05/07/2002 12:44:38

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources