Thank you Phillip, That worked. It seems that I will not be able to just replace the old syntax with the new one, I will have to see that each query actually returns the same result.
Thanks again Victor On 4/23/07, Phillip Ciske <[EMAIL PROTECTED]> wrote: > > 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:276038 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
