JOIN syntax and order for multiple tables

Question

SQL Gurus,

I have a query that uses the "old" style of join syntax as follows using 7 tables (table and column names changed to protect the innocent), as shown below:

SELECT v1_col, p1_col
  FROM p1_tbl, p_tbl, p2_tbl, p3_tbl, v1_tbl, v2_tbl, v3_tbl
  WHERE p1_code = 1
  AND v1_code = 1
  AND p1_date >= v1_date
  AND p_uid = p1_uid 
  AND p2_uid = p1_uid AND p2_id = v2_id
  AND p3_uid = p1_uid AND p3_id = v3_id
  AND v2_uid = v1_uid
  AND v3_uid = v1_uid

The query works just fine and produces the results it is supposed to, but as an academic exercise, I tried to rewrite the query using the more standard JOIN syntax, for example, below is one version I tried:

SELECT V1.v1_col, P1.p1_col
  FROM p1_tbl P1, v1_tbl V1
  JOIN p_tbl P ON ( P.p_uid = P1.p1_uid )
  JOIN p2_tbl P2 ON ( P2.p2_uid = P1.p1_uid AND P2.p2_id = V2.v2_id )
  JOIN p3_tbl P3 ON ( P3.p3_uid = P1.p1_uid AND P3.p3_id = V3.v3_id )
  JOIN v2_tbl V2 ON ( V2.v2_uid = V1.v1_uid )
  JOIN v3_tbl V3 ON ( V3.v3_uid = V1.v1_uid )
 WHERE P1.p1_code = 1
   AND V1.v1_code = 1
   AND P1.p1_date >= V1.v1_date

But, no matter how I arrange the JOINs (using MS SQL 2008 R2), I keep running into the error:

The Multi-part identifier "col-name" could not be bound,

where "col-name" varies depending on the order of the JOINs I am attempting...

Does anyone have any good examples on how use the JOIN syntax with this number of tables??

Thanks in advance!

Answer 1

When you use JOIN-syntax you can only access columns from tables in your current join or previous joins. In fact it's easier to write the old syntax, but it's more error-prone, e.g. you can easily forget a join-condition.

This should be what you want.

SELECT v1_col, p1_col
FROM p1_tbl
JOIN v1_tbl ON p1_date >= v1_date
JOIN v2_tbl ON v2_uid = v1_uid
JOIN v3_tbl ON v3_uid = v1_uid
JOIN  p_tbl ON p_uid = p1_uid 
JOIN p2_tbl ON p2_uid = p1_uid AND p2_id = v2_id
JOIN p3_tbl ON p3_uid = p1_uid AND p3_id = v3_id
WHERE p1_code = 1
  AND v1_code = 1