Problem with left outer join and count()

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)

Problem with left outer join and count()

Author

Topic

LANLord
Starting Member

1 Post

Posted - 2006-04-25 : 20:03:41

I'm having a funky problem combining two tables (really one table and a view). I want all values from the first table, with a count from a second table, if rows exist. If not, I want 0. It seems simple to me, but I'm just not getting it.


crm_reasons:
crm_reason_id complaint
1             y
2             n
3             y
4             y
5             n

crm_ir_with_calldate:
crm_reason_id crm_interaction_id date_called
...
2             100                2006-04-01 12:00:00
3             101                2006-04-02 12:00:00
5             101                2006-04-02 12:00:00
4             102                2006-04-03 12:00:00
3             103                2006-04-04 12:00:00
...

TSQL:


SELECT r.crm_reason_id, if(COUNT(ir.crm_reason_id) IS NOT NULL, COUNT(ir.crm_reason_id), 0) AS count
FROM crm_reasons r
LEFT OUTER JOIN crm_ir_with_calldate ir ON r.crm_reason_id = ir.crm_reason_id
WHERE r.complaint = 'y'
AND ir.date_called BETWEEN '2006-04-01' AND '2006-04-25 23:59:59'
GROUP BY r.crm_reason_id
ORDER BY r.crm_reason_id

Should yield:


crm_reason_id count
1             0
3             2
4             1

But instead, I get:


crm_reason_id count
3             2
4             1

What did I do wrong?

__________________________
Thanks,
CF

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-25 : 20:48:35

Putting the ir.date_called in the WHERE clause will filtered out the record crm_reasons_id = 1


SELECT r.crm_reason_id, count(ir.crm_reason_id)
FROM 	crm_reasons r
LEFT OUTER JOIN crm_ir_with_calldate ir 
ON 	r.crm_reason_id = ir.crm_reason_id
AND 	ir.date_called BETWEEN '2006-04-01' AND '2006-04-25 23:59:59'
WHERE 	r.complaint = 'y'
GROUP BY r.crm_reason_id
ORDER BY r.crm_reason_id

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources