From: ray.bradbury9@gmail.com   
      
   That is right. Combining a LEFT JOIN with a WHERE clause using left joined   
   table defeats the LEFT JOIN purpose because you implicity make the join to   
   behave like an INNER JOIN.   
      
   That is the reason that adding the restriction to the ON is an easy way.   
      
      
   > Solved - I should have check for the date within ON clause, and not in the   
   WHERE   
   > correct version looks like this :   
   >   
   > select ...   
   > left join	@SCHEDULED S	on E.EMPL_ID=S.EMPL_ID   
   >      and S.WORK_DATE = '20150107';   
   >   
   >   
   > On Thursday, February 26, 2015 at 3:51:12 PM UTC-8, M.G. wrote:   
   > > I need to show list of employees along with their scheduled work hours for   
   a given date. If employee was not scheduled to work, then the hours should be   
   empty on his/her line.   
   > >   
   > > Here is my setup (SQL 2008)   
   > > declare @EMPLOYEES	table (   
   > > 	EMPL_ID		int		NOT NULL   
   > > ,	EMPL_NAME	varchar(64)   
   > > )   
   > > insert into @EMPLOYEES(EMPL_ID,EMPL_NAME) values   
   > > ( 100,	'JOHN'),   
   > > ( 200,	'BOB'),   
   > > ( 300,	'MARY');   
   > >   
   > >   
   > > declare @SCHEDULED	table (   
   > > 	EMPL_ID		int		NOT NULL   
   > > ,	WORK_DATE	date	NOT NULL   
   > > ,	WORK_HOURS	int		NOT NULL   
   > > )   
   > > insert into @SCHEDULED(EMPL_ID, WORK_DATE, WORK_HOURS) values   
   > > ( 100,	'20150106',	8),   
   > > ( 100,	'20150107',	8),   
   > > ( 100,	'20150108',	8),   
   > > ( 200,	'20150107',	6);   
   > >   
   > > So, I expect to get three records in my report regardless of the day   
   specified.   
   > >   
   > >   
   > >   
   > > This is my SQL statement:   
   > >   
   > > select E.EMPL_ID, E.EMPL_NAME, S.WORK_DATE, S.WORK_HOURS   
   > > from	   
   > > 		@EMPLOYEES E   
   > > left join	@SCHEDULED S	on E.EMPL_ID=S.EMPL_ID   
   > >   
   > > where		S.WORK_DATE = '20150107' or S.EMPL_ID IS NULL;   
   > >   
   > > It results in :   
   > >   
   > > EMPL_ID	EMPL_NAME WORK_DATE WORK_HOURS   
   > > ======= ======= ========== =======   
   > > 100	JOHN	2015-01-07	8   
   > > 200	BOB	2015-01-07	6   
   > > 300	MARY	NULL	NULL   
   > >   
   > > Which is correct.   
   > >   
   > > If I use 20150206, my result is:   
   > >   
   > > 100	JOHN	2015-01-06	8   
   > > 300	MARY	NULL	NULL   
   > >   
   > > So, I am missing record for BOB, as I would expect to se BOB with NULLS in   
   the report   
   > >   
   > > If I use date of 20150131 I expect to get three lines with all hours being   
   NULLS, but my result is only one record:   
   > >   
   > > 300	MARY	NULL	NULL   
   > >   
   > > Please help me with this situation, any ideas would be appreciated.   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)