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

Re: left join not quite working

X-Received	by 10.236.47.136 with SMTP id t8mr10825354yhb.44.1425002594679; Thu, 26 Feb 2015 18:03:14 -0800 (PST)
X-Received	by 10.50.79.170 with SMTP id k10mr17777igx.5.1425002594392; Thu, 26 Feb 2015 18:03:14 -0800 (PST)
Path	csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!usenet.blueworldhosting.com!feeder01.blueworldhosting.com!border2.nntp.dca1.giganews.com!nntp.giganews.com!i13no8725940qae.0!news-out.google.com!qk8ni43839igc.0!nntp.google.com!hl2no32852412igb.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups	comp.databases.ms-sqlserver
Date	Thu, 26 Feb 2015 18:03:13 -0800 (PST)
In-Reply-To	<c0762c65-7f65-474d-813a-1d399447b7a2@googlegroups.com>
Complaints-To	groups-abuse@google.com
Injection-Info	glegroupsg2000goo.googlegroups.com; posting-host=71.95.163.250; posting-account=99cyNgoAAAA03l-zLDrnoY7TEbs-AvM9
NNTP-Posting-Host	71.95.163.250
References	<c0762c65-7f65-474d-813a-1d399447b7a2@googlegroups.com>
User-Agent	G2/1.0
MIME-Version	1.0
Message-ID	<7b4594f3-98a9-45fe-a455-57098e72204b@googlegroups.com> (permalink)
Subject	Re: left join not quite working
From	"M.G." <michael@gurfinkel.us>
Injection-Date	Fri, 27 Feb 2015 02:03:14 +0000
Content-Type	text/plain; charset=ISO-8859-1
Lines	68
Xref	csiph.com comp.databases.ms-sqlserver:1898

Show key headers only | View raw

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