How can you make a join when also using a comma-separated list of tables in an SQL select statement?

Question

This is obviously the correct syntax in SQL Server:

SELECT a.id, b.name FROM Table1 a, Table2 b WHERE a.id = b.fk1 

Like this:

 SELECT a.id, c.status FROM Table1 a JOIN Table3 c ON a.id = c.fk2 

But this, apparently, is not:

 SELECT a.id, b.name, c.status FROM Table1 a, Table2 b JOIN Table3 c ON a.id = c.fk2 WHERE a.id = b.fk1 

I would usually not want to create a query in the style of the third case (and indeed not the first case), but probably it would be the path of least resistance when editing code that was already written in my company. Someone used the first form with five different tables, and I really need to work in the sixth table using the JOIN statement, without risking spoiling what they already have. Despite the fact that I could rewrite my materials directly, if I need it, I would really like to know how to do something like this in the third case.

The code execution is exactly as it is in the examples, the third case gives me this error message:

 The multi-part identifier "a.id" could not be bound. 

What syntactically violates the third case? What simple fix can be applied? Thanks!

Answer 1

This code:

 SELECT a.id, b.name, c.status FROM Table1 a, Table2 b JOIN Table3 c ON a.id = c.fk2 WHERE a.id = b.fk1 

cross connects on a and the result of the inner join on b and c . c cannot access any of the fields in a because the connection is made on b . what you have to do is change your request to:

 SELECT a.id, b.name, c.status FROM Table1 a inner join Table2 b on a.id = b.fk1 inner JOIN Table3 c ON a.id = c.fk2 

Answer 2

I would also not recommend doing this. But you can just change , to cross join :

 SELECT a.id, b.name, c.status FROM Table1 a cross join Table2 b JOIN Table3 c ON a.id = c.fk2 WHERE a.id = b.fk1