Left outer join problem - SQL Server Forums

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

Transact-SQL (2000)

Left outer join problem

Author

Topic

dbutler_05
Starting Member

17 Posts

Posted - 2006-01-04 : 10:33:22

I'm having a problem returning the data I expect. I know why, just not how to work around it. Here's my script:

SELECT dn.NAME, ds.DailyShipments
FROM FLASH_DetailName AS dn LEFT OUTER JOIN
DailyShipments AS ds ON dn.NAME = ds.NAME
WHERE (ds.DailyShipmentsDt = '12/2/2005')

I have 18 records in FLASH_DetailName and only 10 on DailyShipments. The where clause is causing me to only return 10 records. I need all 18 names with nulls where DailyShipments don't exist for a given date. Seems simple enough, but it's got me stumped.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-01-04 : 10:48:18

[code]
SELECT
dn.NAME,
ds.DailyShipments
FROM
FLASH_DetailName AS dn
LEFT OUTER JOIN
DailyShipments AS ds
ON dn.NAME = ds.NAME AND
ds.DailyShipmentsDt = '2005/12/2'

[/code]

CODO ERGO SUM

dbutler_05
Starting Member

17 Posts

Posted - 2006-01-04 : 10:55:13

Figured it'd be something simple. Thanks.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-01-04 : 11:20:07

never put criteria on the outer tables in a LEFT OUTER JOIN in you WHERE clause. this disallows the effect of the OUTER JOIN since NULLS are filtered out.

see: http://www.sqlteam.com/item.asp?ItemID=11122

for a solution.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources