Need help writing a LEFT JOIN query

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 help writing a LEFT JOIN query

Author

Topic

SBLatta
Starting Member

33 Posts

Posted - 2009-10-12 : 15:22:30

Hello again!

I'm having trouble figuring out how to write a query with a LEFT OUTER JOIN to an intermediary table with then INNER JOIN's back to the original table.

An example may explain it better:

Table 1 (EMPLOYEES)
EmployeeID
ManagerID
LastName
FirstName

Table 2 (MANAGERS)
ManagerID
EmployeeID

Sample Data:
Table 1
1,1,Smith,John
2,,Johnson,Amber

Table 2
1, 2

So, for John Smith, he is EmployeeID 1 and his manager is ManagerID 1. Table 2 shows that ManagerID 1 = EmployeeID 2, or Amber Johnson.

I didn't write the database, and I'm not sure why it is going to a second table to link the ManagerID back to an EmployeeID. In any case, I'm trying to get out:

Employee Manager
------------ --------------
Smith, John Johnson, Amber
Johnson, Amber (Null)

"We're traveling faster than the speed of light, which means by the time I see something, we've already passed through it. Even with an I.Q. of 6000, it's still brown trousers time."

-Holly, Red Dwarf "Future Echoes"

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-10-12 : 15:46:43

[code]declare @EMPLOYEES table (
EmployeeID int,
ManagerID int,
LastName varchar(40),
FirstName varchar(40)
)
insert @EMPLOYEES
select 1,1,'Smith','John' union all
select 2,null,'Johnson','Amber'

declare @MANAGERS table (
EmployeeID int,
ManagerID int
)
insert @MANAGERS
select 1,2

select a.LastName + ', ' + a.FirstName as [Employee],
c.LastName + ', ' + c.FirstName as [Manager]
from @EMPLOYEES a
left join @MANAGERS b on a.EmployeeID = b.EmployeeID
left join @EMPLOYEES c on b.ManagerID = c.EmployeeID[/code]

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2009-10-12 : 15:54:11

SELECT a.LastName + ', ' + a.FirstName AS Employee,
b.LastName + ', ' + b.FirstName AS Manager
FROM EMPLOYEES a
LEFT JOIN (
MANAGERS z
JOIN EMPLOYEES b
ON z.EmployeeID
= b.EmployeeID
)
ON a.ManagerID = z.ManagerID

FYI, vijayisonly has the columns for MANAGERS reversed in his example as far as I can tell from the original post. This was what I interpreted:

declare @MANAGERS table (
ManagerID int,
EmployeeID int

)
insert @MANAGERS
select 1,2

SBLatta
Starting Member

33 Posts

Posted - 2009-10-12 : 16:05:39

Thank you Vijay! That looks like it works perfectly once I got the ManagerID's correct. Lazerath is right, somehow the first link to ManagerID wasn't set up right in your example.

I got my query working though, thanks to you both.

"We're traveling faster than the speed of light, which means by the time I see something, we've already passed through it. Even with an I.Q. of 6000, it's still brown trousers time."

-Holly, Red Dwarf "Future Echoes"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources