conversion *= to left outer joins not yielding desired results

Hi

I have a query which works fine on sql 2000 with *= but doesn't yield the same when converted to 2005 using joins

Here is the original query

SELECT tmpex.pa_number, tmpex.big_deal_id, tmpex.custname, c.iso_code, c.name, pf.product_family_name,
ISNULL(dcts.tatsla, tscd.tatsla), tmpex.psm_contact
FROM country c, product_family pf,
ship_to_country sc, dealcountry_tatsla dcts, tatsla_countrydefaults tscd, #TAT_EXPORT_DEALVALUES tmpex
WHERE
c.id_country = sc.id_country
AND sc.id_deal = tmpex.id_deal

AND sc.id_country *= dcts.id_country

AND sc.id_deal *= dcts.id_deal
AND pf.id_product_family *= dcts.id_product_family
AND sc.id_country = tscd.id_country
AND pf.id_product_family = tscd.id_product_family

The Converted one is below

select tmpex.pa_number, tmpex.big_deal_id, tmpex.custname, c.iso_code, c.name, pf.product_family_name,
ISNULL(dcts.tatsla, tscd.tatsla), tmpex.psm_contact
from
ship_to_country sc INNER JOIN country c
ON sc.id_country = c.id_country
INNER JOIN #TAT_EXPORT_DEALVALUES tmpex
ON (sc.id_deal = tmpex.id_deal )
INNER JOIN tatsla_countrydefaults tscd1
ON (sc.id_country = tscd1.id_country)
LEFT OUTER JOIN
dealcountry_tatsla dcts
ON (sc.id_country = dcts.id_country and sc.id_deal=dcts.id_deal )
LEFT OUTER JOIN product_family pf
ON (pf.id_product_family = dcts.id_product_family)
INNER JOIN tatsla_countrydefaults tscd
ON (pf.id_product_family = tscd.id_product_family)

Please let me know whether I am missing something

The join query results in cartesian product

If I remember old style syntax correctly, the new version will looks like this:

SELECT tmpex.pa_number, tmpex.big_deal_id, tmpex.custname, c.iso_code, c.name, pf.product_family_name,
ISNULL(dcts.tatsla, tscd.tatsla), tmpex.psm_contact
FROM country c
inner join ship_to_country sc on c.id_country = sc.id_country
inner join #TAT_EXPORT_DEALVALUES tmpex on sc.id_deal = tmpex.id_deal
inner join tatsla_countrydefaults tscd on sc.id_country = tscd.id_country
inner join product_family pf on pf.id_product_family = tscd.id_product_family
left join dealcountry_tatsla dcts on sc.id_country = dcts.id_country
and sc.id_deal = dcts.id_deal and pf.id_product_family = dcts.id_product_family

Not sure it is equivalent, though, so thorough testing is highly recommended.|||Your Query seems to be correct. Have you checked the result set. Is there any flaw in that.|||No, I've not checked this - I have no underlying data to test on. If I had, I'd have no doubt

But since you have both the tables and the data, you always may test its correctness - either on MSSQL 2000 or, if all that you have is MSSQL 2005, on a separate database with compatibility level set to 80. In the latter case, both queries will work even on 2005.sqlsql