SQL Server Performance Forum – Threads Archive

script works but not as whole

I’m trying to get this SQL statement to work but not having any luck. If I separate this SQL statement into two separate scripts it works (kind of): Select p.PartID,
p.Description,
m.MfgerID,
m.Description,
v.VendorID,
v.Description,
p.VendorPartID
FROM Part as p, PartPartClass as x, Mfger as m, Vendor as v
where ((p.PartID =* x.PartID) and x.PartClassID = ‘00000022’) and
((m.MfgerID =* p.MfgerID) and (v.VendorID =* p.VendorID)) Part 1: Select p.PartID,
p.Description,
m.MfgerID,
m.Description,
v.VendorID,
v.Description,
p.VendorPartID
FROM Part as p, Mfger as m, Vendor as v
where (m.MfgerID =* p.MfgerID) and (v.VendorID =* p.VendorID) Part 2: SELECT p.PartID, x.PartClassID
FROM Part as p, PartPartClass as x
WHERE (p.PartID =* x.PartID) and x.PartClassID = ‘00000022’
Thanks for your help, Ben

Try to remove =* and rewrite your code using JOIN format. What’s your SQL-Server version? BOL says:
In earlier versions of MicrosoftÂ® SQL Serverâ„¢ 2000, left and right outer join conditions were specified in the WHERE clause using the *= and =* operators. In some cases, this syntax results in an ambiguous query that can be interpreted in more than one way. SQL-92 compliant outer joins are specified in the FROM clause and do not result in this ambiguity. Because the SQL-92 syntax is more precise, detailed information about using the old Transact-SQL outer join syntax in the WHERE clause is not included with this release. The syntax may not be supported in a future version of SQL Server. Any statements using the Transact-SQL outer joins should be changed to use the SQL-92 syntax. CanadaDBA

]]>