Issue With T-SQL JOIN Syntax

Question

I'm upgrading from SQL Server 2000 to SQL Server 2012.  One issue I face is changing the outer join syntax from "*=" to "LEFT OUTER JOIN".

I have a JOIN with more than 1 top level table.  That is, I have a JOIN with more than 1 table that has to be listed in the FROM clause.  I receive the following error message:

            Msg 4104, Level 16, State 1, Line 8

            The multi-part identifier "t.track_id" could not be bound.

If I modify the join, reducing the filtering, so that there is only 1 table listed in the FROM clause, then the query works.

Failing Query (2 tables in FROM clause): MSG 4104

  SELECT

       t.track_code,

       r.race_id,

       track_cond_code

  FROM

       track t,

       track_condition tc

  INNER JOIN oval o ON t.track_id = o.track_id

  INNER JOIN race r ON o.oval_id = r.oval_id

  INNER JOIN r ON tc.track_cond_id = r.track_cond_id

  WHERE

       tc.track_cond_id = 11

Successful Query (1 table in FROM clause):

  SELECT

       t.track_code,

       r.race_id

--     track_cond_code

  FROM

       track t

--     track_condition tc

  INNER JOIN oval o ON t.track_id = o.track_id

  INNER JOIN race r ON o.oval_id = r.oval_id

--  INNER JOIN r ON tc.track_cond_id = r.track_cond_id

--  WHERE

--     tc.track_cond_id = 11

Does T-SQL allow more that 1 table listed in the FROM clause when using the JOIN syntax?

Thanks in advance ...

---

Wayne Baxter

Answer 1

  SELECT
 
       t.track_code,
 
       r.race_id,
 
       track_cond_code
 
  FROM
 
      track t


 
  INNER JOIN oval o ON t.track_id = o.track_id
  INNER JOIN race r ON o.oval_id = r.oval_id
  INNER JOIN track_condition tc ON tc.track_cond_id = r.track_cond_id
 
  WHERE
 
       tc.track_cond_id = 11

Hard to tell but I'm pretty sure that this is what you are trying to do.  Otherwise you have a table named r and a table aliased as r

---

Chuck

Answer 2

And if you are trying to convert old SQL 2000 queries that used = and *= type logic without real joins it would help if you included the old code.  It would make it a lot easier to see what you are trying to do.

---

Chuck

Answer 3

Following are examples for INNER JOIN & LEFT JOIN (new ANSI syntax):

http://www.sqlusa.com/bestpractices2005/innerjoin/

http://www.sqlusa.com/bestpractices/leftjoin/

---

Kalman Toth SQL SERVER & BI TRAINING

Answer 4

There are two problems in your query. First of all, it's important to understand that the FROM clause is all this:

  FROM track t,
       track_condition tc
  INNER JOIN ovalo ON t.track_id= o.track_id
  INNER JOIN racer ON o.oval_id = r.oval_id
  INNER JOIN r ON tc.track_cond_id= r.track_cond_id

That is JOIN is not a separate clause, but it is part of from. In fact this is legal:

  FROM  a
  JOIN  b ON a.col = b.col, c

But (hopefully!) no one writes queries like that.

Within the FROM clause, you cannot use table sourcs in ON until they are introduced. Therefore you need to change the order to:

  FROM track t,
       track_condition tc
  INNER JOIN r ON tc.track_cond_id = r.track_cond_id
  INNER JOIN ovalo ON t.track_id= o.track_id
  INNER JOIN racer ON o.oval_id = r.oval_id

You first need to introduce r before you can refer to it.

However, the query is still OK, because there is also a precedence issue. Consider:

SELECT ...
FROM   A JOIN (B LEFT JOIN C ON A.col1 = B.col1) ON C.col2 = A.col2

The parentheses are not required here, but I put them in for clarity. "B LEFT JOIN C" is a table definition of its own and cannot refer to A, so this will yield an error for A.col1.

In your case, this is more subtle. The comma is an operator just like JOIN, and is better written as CROSS JOIN:

  FROM  track t
  CROSS JOIN track_condition tc
  INNER JOIN ovalo ON t.track_id= o.track_id
  INNER JOIN racer ON o.oval_id = r.oval_id
  INNER JOIN r ON tc.track_cond_id= r.track_cond_id

But JOIN binds tighter than the comma, so your syntax is the same as

  FROM  track t
  CROSS JOIN (track_condition tc
              INNER JOIN ovalo ON t.track_id= o.track_id
              INNER JOIN racer ON o.oval_id = r.oval_id
              INNER JOIN r ON tc.track_cond_id= r.track_cond_id)

Whence the error for t.track_id.

That explains the compile-time errors. But the query still do not make sense. I realise now that I have been lured by the crappy web UI which gladly mangles code pasted into it. (There is a button with two arrows you can use to insert code samples.) So I presume that "ovalo" and "racer" should read "oval o" and "race r".

In that case, this is maybe what you are looking for:

  FROM  track t
  INNER JOIN oval o ON t.track_id= o.track_id
  INNER JOIN race r ON o.oval_id = r.oval_id
  INNER JOIN track_condition tc ON tc.track_cond_id= r.track_cond_id

Except that you talked about old-style outer join, and there is no outer join in sight here.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 5

Please check following link.

It will help you to understand different type of join.

1. http://blog.sqlauthority.com/2010/07/08/sql-server-the-self-join-inner-join-and-outer-join/
2. http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/