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

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

left join not quite working

Show all headers | View raw

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 — 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