Need OUTER JOIN with INNER JOIN (3 tables)

Question

Need OUTER JOIN with INNER JOIN (3 tables)

wink-1151202

Grasshopper

Points: 24
More actions
August 31, 2009 at 6:09 pm

#214474
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:
```
[font="Courier New"]
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
[/font]
```
[font="Courier New"]
```
Data:

EmpID  EmpName     EmpID        OrgID       OrgID  OrgName
--------------   ------------------       ------------------
1001   M. Scott      1001        1            1      PTA
1002   P. Beesly     1001        3            2      IEEE
1003   D. Schrute    1002        2            3      AAA
                     1003        1

Desired output:

EmpName OrgName        OrgID
----------------------------------------
M. ScottPTA1
M. ScottIEEENULL
M. ScottAAA    3
P. BeeslyPTANULL
P. BeeslyIEEE2
P. BeeslyAAANULL
D. SchrutePTA1
D. SchruteIEEENULL
D. SchruteAAANULL
```
[/font]
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!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

wink-1151202 Grasshopper Points: 24 More actions · Answer 1

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

[font="Courier New"]
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
[/font]

drew.allen SSC Guru Points: 77014 More actions · Answer 2

You DO NOT want to do a CROSS JOIN here. The problem with your original query is that a LEFT outer join will return all records from the LEFT side of the join, but your Org table is on the RIGHT side of the join. Changing the LEFT to a RIGHT will fix the problem.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

drew.allen (9/1/2009)
You DO NOT want to do a CROSS JOIN here. The problem with your original query is that a LEFT outer join will return all records from the LEFT side of the join, but your Org table is on the RIGHT side of the join. Changing the LEFT to a RIGHT will fix the problem.
Drew

Tried that myself, didn't get the expected results. The cross join actually does provide the expected results.

Garadin One Orange Chip Points: 29613 More actions · Answer 4

drew.allen (9/1/2009)
You DO NOT want to do a CROSS JOIN here. The problem with your original query is that a LEFT outer join will return all records from the LEFT side of the join, but your Org table is on the RIGHT side of the join. Changing the LEFT to a RIGHT will fix the problem.
Drew

In my opinion, the cross join is the proper way to go in this case. If he only wanted the orgs that the employees were members of, he could use normal outer joins, but his requirement of showing every org for every employee makes this a cross join.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]