Using left join result in a Where clause

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

General SQL Server Forums

New to SQL Server Programming

Using left join result in a Where clause

Author

Topic

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2011-06-22 : 08:38:45

SELECT p.name,q.Position_ID FROM [Position] q
inner join Person p on p.Person_ID = q.Pos_Person_ID
left join Position t on t.Position_ID = q.Pos_ParentPosition_ID

where

t.Position_ID != @SelectedSupervisorPosition and
q.Position_ID != @SelectedSupervisorPosition

How do I do the first line of the Where clause, when there may be no 't' in the result. Currently if there is no Position t in the result, then the first part of the Where clause is excluding the record. Thanks.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-22 : 08:49:41

Move the filters that involve the position table into the join condition as in:

SELECT p.name,q.Position_ID FROM [Position] q
inner join Person p on p.Person_ID = q.Pos_Person_ID
left join Position t on t.Position_ID = q.Pos_ParentPosition_ID
   and t.Position_ID != @SelectedSupervisorPosition
where 
q.Position_ID != @SelectedSupervisorPosition

You may find the information in this blog useful:
http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2011-06-22 : 08:52:22

Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources