Try moving the e.t_date condition up to the join in your first query:
SELECT a.id, a.name, COUNT(e.tid) AS tCT
FROM tbl_a a
LEFT OUTER JOIN tbl_e e
ON a.id = e.id
AND e.t_date = 'Apr 23 2007'
WHERE a.id IN (6,151,706)
GROUP BY a.id, a.name
ORDER BY a.name
This is a difference between ANSI joins and equi-joins. When e.t_date
is null, the SQL Server appropriately sees the WHERE condition
e.t_date = 'Apr 23 2007' as false, so the join fails. Check out
http://www.databasejournal.com/features/mssql/article.php/1438001 for
a much better explanation, especially for outer joins.
On 4/23/07, Victor Moore <[EMAIL PROTECTED]> wrote:
> Hi,
>
> Any particular reason why:
>
> SELECT a.id, a.name, count (e.tid) as tCT
> FROM tbl_a a left outer join tbl_e e ON a.id = e.id
> WHERE a.id in (6,151,706) and
> e.t_date = 'Apr 23 2007'
> GROUP BY a.id, a.name
> ORDER BY a.name
>
> returns only one row for the a.id that matches the where clause while:
>
> SELECT a.id, a.name, count (e.tid) as tCT
> FROM tbl_a a, tbl_e e
> WHERE a.id *= e.id and
> a.id in (6,151,706) and
> e.t_date = 'Apr 23 2007'
> GROUP BY a.id, a.name
> ORDER BY a.name
>
> returns 3 rows as expected
>
> Tested on both SQL 2000 and SQL 2005 and same result
>
> Thanks
> Victor
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
ColdFusion MX7 by Adobe®
Dyncamically transform webcontent into Adobe PDF with new ColdFusion MX7.
Free Trial. http://www.adobe.com/products/coldfusion?sdid=RVJV
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:276034
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
