2

Is it possible to select multiple tables and make inner join on one of those tables? e.g.

SELECT * 
FROM table1 AS t1, table2 AS t2, table3 AS t3
INNER JOIN table4 AS t4 ON t1.row3 = t4.row3
INNER JOIN table5 AS t5 ON t1.row4 = t5.row4
WHERE ...

This paricular case is causing me a problem. it gives me an error - Unknown column "t1.row3" in 'on clause'. I don't know if it's possible to select multiple tables but make inner join on one of those tables.

woopata
  • 875
  • 5
  • 17
  • 29
  • Are these your actual table and column names? – klennepette Jan 28 '12 at 12:44
  • no, these are not my actual tables and column names but it's similar. I need to select 3 tables, on one table i need to do inner join, and on other two tables just simple select. – woopata Jan 28 '12 at 16:41

1 Answers1

10

The JOIN operand has higher precedence than comma , operand, so the join is effectively treated as

t1, t2, (t3, t4, t5 ON ... )

Put parentheses around t1, t2, t3.

SELECT * 
FROM  ( table1 AS t1, table2 AS t2, table3 AS t3 )
INNER JOIN table4 AS t4 ON t1.row3 = t4.row3
INNER JOIN table5 AS t5 ON t1.row4 = t5.row4
WHERE ...

You can also write your query as:

SELECT * 
FROM  table1 AS t1
INNER JOIN table2 AS t2
INNER JOIN table3 AS t3
INNER JOIN table4 AS t4 ON t1.row3 = t4.row3
INNER JOIN table5 AS t5 ON t1.row4 = t5.row4
WHERE ...

because comma is equivalent to INNER JOIN without a join condition.

piotrm
  • 12,038
  • 4
  • 31
  • 28
  • actually what i didnt see that now as results i get same row multiple times... row1,row1,row1,row2,row2,row2.... – woopata Jan 28 '12 at 16:40