Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Using Outer Joins instead of asterisks on multiple tables

Posted on 2007-11-29
4
Medium Priority
?
789 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 1500 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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

972 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