Solved

Using Outer Joins instead of asterisks on multiple tables

Posted on 2007-11-29
4
782 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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server 2012 r2 - Make Temp Table Query Faster 5 40
sql 2014,  lock limit 5 29
2016 SQL Licensing 7 40
SQL - Use results of SELECT DISTINCT in a JOIN 4 14
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

785 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