Need to join 3 tables - OUTER JOIN with INNER JOIN

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 2005 Forums

Transact-SQL (2005)

Need to join 3 tables - OUTER JOIN with INNER JOIN

Author

Topic

wink321
Starting Member

2 Posts

Posted - 2009-08-31 : 20:53:50

Hi,

I am trying to join 3 tables (Employee, EmployeeOrg, and Org) structured below. I need to return all Org names no matter the membership, so an outer join definitely should apply here, but it's not working.

The dev environment uses SQL Server Express 2005, but Production will be SQL Server 2005. The query is:

SELECT E.EmpName, A.OrgName, EA.OrgID
FROM EmployeeOrg EA
INNER JOIN Employee AS E
ON EA.EmpID = E.EmpID
LEFT OUTER JOIN Org AS A
ON EA.OrgID = A.OrgID

Data:

EmpID EmpName
--------------
1001 M. Scott
1002 P. Beesly
1003 D. Schrute

EmpID OrgID
------------------
1001 1
1001 3
1002 2
1003 1

OrgID OrgName
------------------
1 PTA
2 IEEE
3 AAA

Desired output:

EmpName OrgName OrgID
----------------------------------------
M. Scott PTA 1
M. Scott IEEE NULL
M. Scott AAA 3
P. Beesly PTA NULL
P. Beesly IEEE 2
P. Beesly AAA NULL
D. Schrute PTA 1
D. Schrute IEEE NULL
D. Schrute AAA NULL

It seems to be treating the left outer join as a regular join (not returning any NULLs). If I just join the OrgName and OrgID tables I do get some nulls, but I really need the additional join to the Emp table. I've read many examples and articles online and this should be working but maybe I'm missing something on the syntax. Any help or pointers would be appreciated!

wink321
Starting Member

2 Posts

Posted - 2009-08-31 : 21:09:48

Rudy of r937.com replied on another forum with the following answer, which worked!

SELECT E.EmpName
, A.OrgName
, EA.OrgID
FROM Employee AS E
CROSS
JOIN Org AS A
LEFT OUTER
JOIN EmployeeOrg EA
ON EA.EmpID = E.EmpID
AND EA.OrgID = A.OrgID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources