Solved

Using Outer Joins instead of asterisks on multiple tables

Posted on 2007-11-29
4
780 Views
Last Modified: 2012-08-14
I have a query which I would like to update to use 'Outer Joins' for compatablity with SQL Server 2005/2008. I have always been used to using asterisks and I understand the basics of the Outer Joins, but not on a select query with multiple inner joins as well.

Any advice how to replace the asterisks with the 'from outer Join on' and maintaining the WHERE inner joins on the attached code snippet would be appreciated.

Thanks

SELECT          PT.CASE_ID,

	        PC.CASE_TYPE_ID,

	        PT.NUMBER,

         	PC.NUMBER,

         	PC.WORD,

         	PT.REF,

         	SN.STATE_NAME,

         	PT.CLASS,

         	PA.ABSTRACT, 

         	TMC.CATEGORY_LABEL,

         	RT.REGARDING_TEXT,

         	TM.TEAM_LABEL, 

         	TT.TITLE_TEXT, 

        	PT.APPL_DATE, 

         	PT.APPL_NO, 

         	PT.DIARY_TEXT3, 

         	PT.PRIO_COUNTRY, 

         	PT.PRIO_DATE, 

         	PT.PRIO_NO , 

         	PT.DIARY_DATE9, 

         	PT.DIARY_TEXT9, 

         	PT.REG_DATE, 

         	PT.REG_NO, 

         	PC.REMARK, 

         	PT.DURATION, 

         	ST.STATUS,

         	PT.START,

         	PT.ENDDATE,

	        ATY.NAME
 

FROM 	        SQL1.HLLIVE.DBO.PAT_CASE PC,

         	SQL1.HLLIVE.DBO.PAT_REPORT_TABLE PT,

         	SQL1.HLLIVE.DBO.CASTING CAS,

         	SQL1.HLLIVE.DBO.ACTOR ACT,

         	SQL1.HLLIVE.DBO.ACTORS_PROFILE APR,

         	SQL1.HLLIVE.DBO.PROFILE_TYPE PRT,

         	SQL1.HLLIVE.DBO.PAT_STATUS_TEXT ST,

         	SQL1.HLLIVE.DBO.STATE_NAME SN,

        	SQL1.HLLIVE.DBO.CASE_TYPE_DEFINITION CTD, 

         	SQL1.HLLIVE.DBO.WORK_GROUP WG,

         	SQL1.HLLIVE.DBO.TEAM TM, 

         	SQL1.HLLIVE.DBO.TITLE_TEXT TT,

         	SQL1.HLLIVE.DBO.REGARDING_TEXT RT,

         	SQL1.HLLIVE.DBO.PAT_ABSTRACT PA,

         	SQL1.HLLIVE.DBO.ACTORS_SERVICES ASS,

         	SQL1.HLLIVE.DBO.CASE_TM_CATEGORY TMC,

         	SQL1.HLLIVE.DBO.APPLICATION_TYPE ATY,

	        SQL1.HLLIVE.DBO.DIARY_TEXT DT

	

WHERE 

    	ATY.TYPE_ID = PC.TYPE_ID

     	AND PT.CASE_ID *= TT.CASE_ID

        AND PC.TRADE_MARK_CATEGORY *= TMC.TRADE_MARK_CATEGORY 

     	AND PC.CASE_ID *= PA.CASE_ID

     	AND PC.CASE_ID *= RT.CASE_ID

     	AND PC.STATE_ID = SN.STATE_ID

     	AND PC.CASE_TYPE_ID = CTD.CASE_TYPE_ID

    	AND PC.CASE_ID = WG.CASE_ID

     	AND WG.TEAM_ID = TM.TEAM_ID

     	AND ASS.ACTOR_ID = ACT.ACTOR_ID

     	AND ASS.SERVICES_TYPE_ID = PC.CASE_TYPE_ID

     	AND ACT.ACTOR_ID = CAS.ACTOR_ID 

     	AND ACT.ACTOR_ID = APR.ACTOR_ID

     	AND CAS.CASE_ID = PC.CASE_ID

     	AND PC.CASE_ID = PT.CASE_ID

     	AND APR.PROFILE_TYPE_ID = PRT.PROFILE_TYPE_ID

     	AND PRT.LABEL = ST.LABEL

     	AND ST.STATUS_ID = PC.STATUS_ID

	AND DT.CASE_ID = PT.CASE_ID

Open in new window

0
Comment
Question by:paulandyt
  • 2
4 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20374060
the easiest way is , right click any of the tables -> query -> and paste the above query and click the run icon , that's it !! copy the code
0
 

Author Comment

by:paulandyt
ID: 20375119
I was hoping that there would be a future proof way of doing it. As you may have seen before, when running the script on the SQL Server 2005, it produces the following message. I can get rid of the message by enabling backwards compatibility, but would prefer not to.



"The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes."

Open in new window

0
 
LVL 3

Accepted Solution

by:
IUFITS earned 500 total points
ID: 20375496
I'll give you an example of how to accomplish this in the attached code snippet.  This select statement joins 3 tables with 2 inner joins and 1 left outer join.  The left outer joined table returns all records from the main table and nulls for values in the left joined table if it doesn't find a matching record.  If you don't want nulls, then you can use Coalesce to return default values (e.g. Select Coalece(LogEntry, '') As LogEntry).
Select EmployeeID, FirstName, LastName, Address1, HomePhone, LogEntry

From Employee

Inner Join EmployeeAddress On EmployeeAddress.EmployeeID = Employee.EmployeeID

Inner Join EmployeePhone On EmployeePhone.EmployeeID = Employee.EmployeeID

Left Outer Join LogEntries On LogEntries.EmployeeID = Employee.EmployeeID

Open in new window

0
 

Author Closing Comment

by:paulandyt
ID: 31411674
Thanks that was great! I wasn't sure how to put inner joins with outer joins, and it looks like I don't need to use the where command, just ON with the join type, the table and the joining tables.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now