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!

Ok - edited to show one attempt using table aliases -- but the aliases make no difference — bdcoder, May 19 '14 at 17:22
You are using `AND P2.p2_id = V2.v2_id` before the `v2` table is referenced in the `FROM`. Try moving that condition in the `ON` clause when the `V2` is defined/referenced. — ypercubeᵀᴹ, May 19 '14 at 17:29
The `FROM p1_tbl P1, v1_tbl V1` will get you into trouble, too. Change it to `FROM p1_tbl P1 JOIN v1_tbl V1 ON P1.p1_date >= V1.v1_date` — ypercubeᵀᴹ, May 19 '14 at 17:32

score 0 · Answer 1 · answered May 19 '14 at 16:48

0

You are not naming the tables in your join such that it doesn't know which column is from which table. Try something like:

SELECT a.v1_col, b.p1_col
FROM   p1_tbl  b
JOIN   p_tbl   a  ON  b.p_uid = a.p1_uid

WHERE  b.p1_code = 1

answered May 19 '14 at 16:48

Brian Gunter

83
8

score 0 · Answer 2 · answered May 19 '14 at 16:58

0

From your query above, I am assuming a naming convention of p2_uid comes from p2_tbl. Below id my best interpretation of WHERE joins to using INNER joins.

SELECT
    v1_col, p1_col
FROM
    p1_tbl

    INNER JOIN p1_tbl
    ON p1_tbl.p1_date   >= v1_tbl.v1_date

    INNER JOIN p_tbl
    ON p_tbl.p_uid      = p1_tbl.p1_uid

    INNER JOIN p2_tbl
    ON  p2_tbl.p2_uid   = p1_tbl.p1_uid 

    INNER JOIN v2_tbl
    ON p2_tbl.p2_id     = v2_tbl.v2_id

    INNER JOIN p3_tbl
    ON p3_tbl.p3_uid = p1_tbl.p1_uid

    INNER JOIN v3_tbl
    ON p3_tbl.p3_id = v3_tbl.v3_id

    INNER JOIN v1_tbl
    ON  v1_tbl.v1_uid = v2_tbl.v2_uid
    AND v1_tbl.v1_uid = v3_tbl.v2_uid

WHERE
    p1_code = 1
AND
    v1_code = 1

Some general points I have found useful in SQL statements with many joins.

Always fully qualify the names. I.e dont use ID , rahter use TableName.ID
Dont use aliases unless there is meaning. (I.e. joining a table to its self where aliasing is needed.)

answered May 19 '14 at 16:58

Richard Vivian

1,700
1
14
19

2

I agree with your first bullet point, but disagree with the second. Using the full table name adds a lot of useless text, and if your query analyzer lacks code completion (or lacks good code completion) it also adds a lot of typing. In this case, using aliases p1, p2, v1, v2, v3 significantly increases readability to me. – Bacon Bits May 19 '14 at 17:08
The second is a point of preference rather than a rule. But I have run into so may places where tables are aliased as A, B. Or IT which can be InventoryTrans, InvenoryTable etc that I have found just sticking to the fully qualified actual object names has worked for me, and is very maintainable. But as noted, its a preference. – Richard Vivian May 19 '14 at 17:41
Oh, yes, it's certainly possible for people to be idiots about alias selection. – Bacon Bits May 19 '14 at 18:35

dnoeth · Accepted Answer · 2014-05-19T17:27:09.973

0

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

edited May 19 '14 at 17:27

answered May 19 '14 at 17:21

dnoeth

59,503
4
39
56

Thanks to the experts that responded -- My ignorance with the JOIN syntax was I (mistakenly) thought the parser could "join" no matter what the order (never seemed to be an issue with the old way) -- but the order of the JOINs IS significant as stated in this answer. Thanks again! – bdcoder May 19 '14 at 21:36

JOIN syntax and order for multiple tables

3 Answers3