Getting ZERO Count back for Non Existing Rows.

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

Transact-SQL (2005)

Getting ZERO Count back for Non Existing Rows.

Author

Topic

batcater98
Starting Member

22 Posts

Posted - 2007-08-31 : 16:17:25

I have two tables - one with a list of all existing numbers ranging from 700 to 8000. The other table actually contains data with records that have these numbers in a column that could repeat over and over again. I am trying to use the reference table (equipped) to COUNT through the (DATA) table and return how many times it finds the value 700, 701.... in field data.num.

This all worked great until I put the WHERE clause on with a date range, now it is not pulling back any Zero counts and I know for a fact that they are not there.

Code:

SELECT equipped_loco.Equipped_Loco, COUNT(event_data.Loco_Num)AS
Loco_Count
FROM equipped
LEFT OUTER JOIN data
ON equipped.Equipped = data.Num
WHERE(data.DN_Date >= '8/1/2007') AND (data.DN_Date <= '8/31/2007')
GROUP BY equipped.Equipped
ORDER BY equipped.Equipped

why does this work without the WHERE & date range?

Thanks,
Ad.

sshelper
Posting Yak Master

216 Posts

Posted - 2007-08-31 : 16:23:29

Try putting your WHERE clause inside the JOIN clause:

SELECT equipped_loco.Equipped_Loco, COUNT(event_data.Loco_Num)AS
Loco_Count
FROM equipped
LEFT OUTER JOIN data
ON equipped.Equipped = data.Num
AND (data.DN_Date >= '8/1/2007') AND (data.DN_Date <= '8/31/2007')
GROUP BY equipped.Equipped
ORDER BY equipped.Equipped

SQL Server Helper
http://www.sql-server-helper.com

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-31 : 16:24:04

because in your where you're referencing the data table you're left joining to thus making it an inner join.
try this:
SELECT equipped_loco.Equipped_Loco, COUNT(event_data.Loco_Num)AS
Loco_Count
FROM equipped
LEFT OUTER JOIN data ON equipped.Equipped = data.Num and (data.DN_Date >= '8/1/2007') AND (data.DN_Date <= '8/31/2007')
GROUP BY equipped.Equipped
ORDER BY equipped.Equipped

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-31 : 16:24:25

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources