CODEWITHSUNDEEP
phpmysqlsqldatabase
JH95
JH95

Reputation: 489

MySQL ERROR: 'Unkown column' tablename.colname in 'on clause'

I am trying to join 2 tables to another table. I have this SQL query:

SELECT
desc_1.description,
desc_2.description,
Object.objID,
Item.itemID
FROM
Object,
Item 
INNER JOIN Foo desc_1 ON desc_1.descID = Object.objDescID
INNER JOIN Foo desc_2 ON desc_2.descID = Item.itemDescID;

However i get the following error:

ERROR 1054: Unkown column 'Object.objDescID' in 'on clause'

Desired Output:

description | description | objID | itemID |
------------+-------------+-------+--------+      
 "Daughter" |   "Shoes"   |   20  |   25   |

Does anyone know why this is happening? The column exists in the table i have checked spelling etc.

Upvotes: 0

Views: 48

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

If your query is what you want, then replace the , with cross join:

SELECT desc_1.description, desc_2.description,
       Object.objID, Item.itemID
FROM Object CROSS JOIN
     Item INNER JOIN
     Foo desc_1
     ON desc_1.descID = Object.objDescID INNER JOIN
     Foo desc_2
     ON desc_2.descID = Item.itemDescID;

The documentation even explains this, although I admit it is a bit hard to find:

However, the precedence of the comma operator is less than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur. Information about dealing with this problem is given later in this section.

Upvotes: 0

juergen d
juergen d

Reputation: 204756

You can't mix legacy implicit join syntax and the explicit join syntax. Use

SELECT desc_1.description,
       desc_2.description,
       Object.objID,
       Item.itemID
FROM Object
INNER JOIN Item on { fill in the column that relate object and item }
INNER JOIN Foo desc_1 ON desc_1.descID = Object.objDescID
INNER JOIN Foo desc_2 ON desc_2.descID = Item.itemDescID;

Upvotes: 1

Related Questions