• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 794
  • Last Modified:

Using Outer Joins instead of asterisks on multiple tables

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
paulandyt
Asked:
paulandyt
  • 2
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
paulandytAuthor Commented:
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
 
IUFITSCommented:
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
 
paulandytAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now