Converting *= to left join-CodePudding

I have updated my sql server from 2008 to 2019. i get the following error when running a store procedure

select pr.productID,@eoddate,tblSTlocation.locationID,0,0,0,0,0,0,operatorID,null,0,0,valprice 
from tblSTproduct pr,tblSTcosting co, tblSLoperator, tblSTlocation 
where pr.productID*=co.productID

how do i convert the *= to a left join?

CodePudding user response：

I don't have an execution environment handy, so I can't test this, but I believe this should work:

select pr.productID,@eoddate,tblSTlocation.locationID,0,0,0,0,0,0,operatorID,null,0,0,valprice 
from tblSTproduct pr, tblSLoperator, tblSTlocation 
left outer join tblSTcosting co on pr.productID = co.productID

I would also recommend making the tblSLoperator and tblSTlocation joins explicit.

CodePudding user response：

If I'm reading it right, what you had was equivalent to this:

SELECT pr.productID,@eoddate,tblSTlocation.locationID,0,0,0,0,0,0,operatorID,null,0,0,valprice 
FROM tblSTproduct pr
LEFT JOIN tblSTcosting co ON pr.productID =co.productID
CROSS JOIN tblSLoperator
CROSS JOIN tblSTlocation

... which seems VERY strange. It's very much not normal to see CROSS JOIN like that, especially twice. Are we missing some conditions in the WHERE clause that might let us write those as INNER JOIN?