MS SQL Server trouble with JOIN

Question

I'm new to SQL and need a push in the right direction.

I currently have a working SQL query accessing 3 tables in a database, and I need to add a JOIN using a 4th table, but the syntax escapes me. To simplify, what I have now is:

SELECT 
    t1_col1, t1_col2, t2_col1, t2_col2, t3_col1 
FROM 
    table1, table2, table3 
WHERE 
    {some conditions} 
ORDER BY 
    t1_col1 ASC;

What I need to do is to add a LEFT OUTER JOIN selecting 2 columns from table4 and have ON t1_field1 = t4_field1, but whatever I try, I'm getting syntax errors all over the place. I don't seem to understand the correct syntax.

I tried

SELECT * 
FROM table1 
LEFT OUTER JOIN table2;

which has no errors, but as soon as I start SELECTing columns and adding conditions, I get stuck.

I would greatly appreciate any assistance with this.

Do the tables have matching field names? If so, you'll have to qualify the table names (you should be doing that anyway). For example, `SELECT Table1.Field1, Table2.Field1, Table1.Field2 FROM Table1 LEFT JOIN Table2 ON Table1.KeyField = Table2.KeyField WHERE Table1.FieldX = 'X'` — basodre, Feb 19 '21 at 20:03
Thank you @basodre. None of the field names match. Each one has part of the table name as part of the field name. I tried prepending table names, but ended up with "Multi-part identifier could not be bound", so I figured that wouldn't help. — user15244939, Feb 19 '21 at 20:11
Start over- No one should be using [old-style joins](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins). Replace your original query with the appropriate JOIN syntax. Once you do that (and it works correctly), adding an outer join should be trivial. — SMor, Feb 19 '21 at 21:03

score 0 · Accepted Answer · answered Feb 20 '21 at 00:50

You do not specify the join criteria. Those would be in your WHERE clause under "some conditions". So, I will make up so that I can show syntax. The syntax you show is often termed "old". It has been discouraged for 15 years or more in the SQL Server documentation. Microsoft consistently threatens to stop recognizing the syntax. But, apparently they have not followed through on that threat.

The syntax errors you are getting occur because you are mixing the old style joins (comma separated with WHERE clause) with the new style (LEFT OUTER JOIN) with ON clauses.

Your existing query should be changed to something like this. The tables are aliased because it makes it easier to read and is customary. I just made up the JOIN criteria.

SELECT t1_col1, t1_col2, t2_col1, t2_col2, t3_col1 
FROM table1 t1
INNER JOIN table2 t2 ON t2.one_ID = t1.one_ID
INNER JOIN table3 t3 ON t3.two_ID = t2.two_ID
LEFT OUTER JOIN table4 t4 ON t4.three_ID = t3.three_ID

I hope that helps with "a push in the right direction."

user15282382 · Answer 2 · 2021-03-11T03:22:25.937

You may also want to read this post that explains the different ways to join tables in a query. What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

Also for the record the "OLD STYLE" of joining tables (NOT RECOMMENDED) would look like this (but do NOT do this - it is a horrible way to write SQL). And it does not work for left outer joins. Get familiar with using the ...JOIN...ON... syntax:

SELECT t1_col1, t1_col2, t2_col1, t2_col2, t3_col1 
FROM table1 t1, table2 t2, table3 t3
LEFT OUTER JOIN table4 t4 ON t4.three_ID = t3.three_ID
WHERE
  t2.one_ID = t1.one_ID
  AND t3.two_ID = t2.two_ID

MS SQL Server trouble with JOIN

2 Answers2