Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1899

Re: left join not quite working

Show all headers | View raw

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.

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

left join not quite working "M.G." <michael@gurfinkel.us> - 2015-02-26 15:51 -0800
  Re: left join not quite working "M.G." <michael@gurfinkel.us> - 2015-02-26 18:03 -0800
    Re: left join not quite working bradbury9 <ray.bradbury9@gmail.com> - 2015-02-27 00:16 -0800
    Re: left join not quite working Gene Wirchenko <genew@telus.net> - 2015-02-27 10:23 -0800

csiph-web