Converting *= to left join

Question

I have updated my SQL Server from 2008 to 2019.

I get the following error when running a stored 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?

No conditions for the other tables? Do you really want a cartesian product (every possible combination of all rows) for those tables? — Joel Coehoorn, Mar 06 '22 at 02:33
Does this answer your question? [What is this operand (\*= star-equals) in SQL server 2000?](https://stackoverflow.com/questions/6269845/what-is-this-operand-star-equals-in-sql-server-2000) — Charlieface, Mar 06 '22 at 02:58
It is way past time for your organization to use best practices when writing code. You have to change this statement so you might as well drag the whole thing into the 21 century. Schema-qualify your tables, create a short (but not cryptic) alias for each table and use it for every column reference, use statement terminators, etc. The ridiculous naming standard should also be left behind. — SMor, Mar 06 '22 at 12:04
The error you provided looks very much like a `select` statement. You haven't provided the stored procedure that you are running. Please read [this](https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. — HABO, Mar 06 '22 at 16:38

score 4 · Answer 1 · answered Mar 06 '22 at 02:36

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?

score 0 · Answer 2 · answered Mar 06 '22 at 01:34

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.

Converting *= to left join

2 Answers2