Using LEFT JOIN with WHERE filter

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

Transact-SQL (2008)

Using LEFT JOIN with WHERE filter

Author

Topic

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2014-03-21 : 12:27:39

The following script below I am creating so that it will return all Employee's of the filtered manager from table 'dbEmployee.Summary.tblEmployeeSnapshot' rather or not they have a record on table 'DetailPBS.tblEmployeeBidPeriodhist' or not. I am currently using LEFT JOINs to acheive this, but the problem I'm having is that when I set my filters up in the WHERE clause. It turns into a INNER JOIN and I lose my NULL columns. How can I get around this, so that I can filter on the tables needed?


DECLARE @ManagerID varchar(20)
DECLARE @Today DATE

SET  @ManagerID = '323031'
Set @Today = (Select DATEADD(Day,DATEDIFF(Year, -2, getDate()),0))

SELECT   snp.Employeeid 
      ,b.BidID
      ,bg.BidgroupName + ' - ' + CONVERT(VARCHAR,b.BidStartdate,101 ) AS 'Bidgroup'
	  ,max(BidStartdate) BidStartDate
	  ,MAX(bg.BidgroupName) BidGroupName


FROM dbEmployee.Summary.tblEmployeeSnapshot snp

LEFT JOIN dbMyInfo.DetailPBS.tblEmployeeBidPeriodhist hist
       ON hist.EmployeeID = snp.Employeeid
       
LEFT JOIN ConfigPBS.tblbids              b
        ON b.BidID = hist.Bidid
   
        
LEFT JOIN ConfigPBS.tblBidGrouping       bg
  		 ON b.Bidgroupid = bg.BidGroupid 
  		 
LEFT JOIN ConfigPBS.tblBidGroupLocationMapping bgl
		 ON bg.BidGroupid = bgl.BidGroupid
		 
LEFT JOIN ConfigPBS.tblFunctionalGroupMapping fg
		 ON bg.FunctionalGroupid = fg.FunctionalGroupid
          	
  		 
  		 
WHERE /*b.isactive = 1       --- How can I filter on these field without it creating a INNER JOIN
        AND fg.IsActive = 1                 
		and b.BidLockout >= @Today*/		
	 (BottomUp01ID = @ManagerID
			or BottomUp02ID = @ManagerID
			or BottomUp03ID = @ManagerID
			or BottomUp04ID = @ManagerID
			or BottomUp05ID = @ManagerID)
						
Group BY
        snp.Employeeid,
		b.BidID,
		bg.BidgroupName + ' - ' + CONVERT(VARCHAR,b.BidStartdate,101 ) 
		ORDER BY max(BidStartdate), MAX(bg.BidgroupName)

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-21 : 13:46:04

Include those where clause conditions in the JOIN, like shown below:

.....
LEFT JOIN dbMyInfo.DetailPBS.tblEmployeeBidPeriodhist hist
       ON hist.EmployeeID = snp.Employeeid
       
LEFT JOIN ConfigPBS.tblbids              b
        ON b.BidID = hist.Bidid AND b.isactive = 1 AND b.BidLockout >= @Today
   
        
LEFT JOIN ConfigPBS.tblBidGrouping       bg
  		 ON b.Bidgroupid = bg.BidGroupid 
  		 
LEFT JOIN ConfigPBS.tblBidGroupLocationMapping bgl
		 ON bg.BidGroupid = bgl.BidGroupid
		 
LEFT JOIN ConfigPBS.tblFunctionalGroupMapping fg
		 ON bg.FunctionalGroupid = fg.FunctionalGroupid AND fg.IsActive = 1 
          	
  		 
  		 
WHERE /*b.isactive = 1       --- How can I filter on these field without it creating a INNER JOIN
        AND fg.IsActive = 1                 
		and b.BidLockout >= @Today*/		
	 (BottomUp01ID = @ManagerID
....

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2014-03-21 : 14:30:08

When I do that I receive dups, any ideas why?

quote:

Originally posted by James K

Include those where clause conditions in the JOIN, like shown below:

.....
LEFT JOIN dbMyInfo.DetailPBS.tblEmployeeBidPeriodhist hist
       ON hist.EmployeeID = snp.Employeeid
       
LEFT JOIN ConfigPBS.tblbids              b
        ON b.BidID = hist.Bidid AND b.isactive = 1 AND b.BidLockout >= @Today
   
        
LEFT JOIN ConfigPBS.tblBidGrouping       bg
  		 ON b.Bidgroupid = bg.BidGroupid 
  		 
LEFT JOIN ConfigPBS.tblBidGroupLocationMapping bgl
		 ON bg.BidGroupid = bgl.BidGroupid
		 
LEFT JOIN ConfigPBS.tblFunctionalGroupMapping fg
		 ON bg.FunctionalGroupid = fg.FunctionalGroupid AND fg.IsActive = 1 
          	
  		 
  		 
WHERE /*b.isactive = 1       --- How can I filter on these field without it creating a INNER JOIN
        AND fg.IsActive = 1                 
		and b.BidLockout >= @Today*/		
	 (BottomUp01ID = @ManagerID
....

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-03-21 : 14:42:44

That would be because for a given EmployeeId in dbEmployee.Summary.tblEmployeeSnapshot you have more than one row in dbMyInfo.DetailPBS.tblEmployeeBidPeriodhist, or for a given BidID in dbMyInfo.DetailPBS.tblEmployeeBidPeriodhist you have more than one row in ConfigPBS.tblbids and so on. IN other words, somewhere in your joins there is a one-to-many relationship rather than one-to-one.

You have to decide which of those many you want and filter accordingly so it will be a one-to-one join.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-03-21 : 15:27:36

quote:
Originally posted by ScottPletcher

[quote]WHERE /*b.isactive = 1 --- How can I filter on these field without it creating a INNER JOIN
AND fg.IsActive = 1
and b.BidLockout >= @Today*/

You can also check for NULL in the WHERE:

WHERE (b.BidID IS NULL OR (b.isactive = 1 AND b.BidLockout >= @Today)) AND
(fg.FunctionalGroupid IS NULL OR (fg.IsActive = 1))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources