Error on JOIN mysql

Question

Can you help me to fix this code?

SELECT Articolisti_Articoli.*
FROM Articolisti_Articoli, Articolisti_Incarichi
LEFT JOIN SitiWeb_Articoli
ON SitiWeb_Articoli.ArticoloID = Articolisti_Articoli.ID
WHERE SitiWeb_Articoli.ArticoloID IS NULL AND Articolisti_Articoli.IncaricoID = Articolisti_Incarichi.ID AND Articolisti_Incarichi.CategoriaID = 15

I receive this error:

#1054 - Unknown column 'Articolisti_Articoli.ID' in 'on clause'

But if I try the simple version:

SELECT Articolisti_Articoli.*
FROM Articolisti_Articoli
LEFT JOIN SitiWeb_Articoli
ON SitiWeb_Articoli.ArticoloID = Articolisti_Articoli.ID
WHERE SitiWeb_Articoli.ArticoloID IS NULL

...it works! :S

Thanks!

score 1 · Answer 1 · answered Aug 26 '13 at 22:10

Seems like you mixed two various solutions for retrieveing data: with where and join clauses and the second strange I mentioned is choosing only null articolod ids which probably cannot be null (SitiWeb_Articoli.ArticoloID IS NULL)

consider this one:

 SELECT Articolisti_Articoli.*
 FROM Articolisti_Articoli 
   LEFT JOIN Articolisti_Incarichi
     ON Articolisti_Articoli.IncaricoID = Articolisti_Incarichi.ID
   LEFT JOIN SitiWeb_Articoli
    ON SitiWeb_Articoli.ArticoloID = Articolisti_Articoli.ID
   WHERE Articolisti_Incarichi.CategoriaID = 15

and check for the column 'ID' in table 'Articolisti_Articoli' for 'not null' closure.

The `WHERE Articolisti_Incarichi.CategoriaID = 15` should go into the `JOIN` condition otherwise the (left) outer join will be turned into an inner join. — , Aug 26 '13 at 22:13

score 1 · Answer 2 · answered Aug 26 '13 at 22:47

This is documented:

http://dev.mysql.com/doc/refman/5.0/en/join.html

Previously, the comma operator (,) and JOIN both had the same precedence, so the join expression t1, t2 JOIN t3 was interpreted as ((t1, t2) JOIN t3). Now JOIN has higher precedence, so the expression is interpreted as (t1, (t2 JOIN t3)). This change affects statements that use an ON clause, because that clause can refer only to columns in the operands of the join, and the change in precedence changes interpretation of what those operands are.

So a query will fail in this form:

SELECT ... FROM A, B JOIN C ON A.x = C.y

The best fix is to use JOIN syntax consistently:

SELECT ... FROM A JOIN B ON ... JOIN C ON A.x = C.y

And in your example I would write it like this:

SELECT aa.*
FROM Articolisti_Articoli aa
INNER JOIN Articolisti_Incarichi ai ON aa.IncaricoID = ai.ID
LEFT JOIN SitiWeb_Articoli sa ON sa.ArticoloID = aa.ID
WHERE sa.ArticoloID IS NULL AND ai.CategoriaID = 15

score 0 · Answer 3 · answered Aug 26 '13 at 22:19

You are using a straight join

SELECT * FROM table1,table2 WHERE table1.id=table2.id;

Try writing the query with an explicity ON clause for the join statement

SELECT 
    Articolisti_Articoli . *
FROM
    Articolisti_Articoli
        INNER JOIN
    Articolisti_Incarichi ON Articolisti_Articoli.{join column} = Articolisti_Incarichi.{other join column}
        LEFT JOIN
SitiWeb_Articoli ON SitiWeb_Articoli.ArticoloID = Articolisti_Articoli.ID
WHERE
    SitiWeb_Articoli.ArticoloID IS NULL AND Articolisti_Articoli.IncaricoID = Articolisti_Incarichi.ID AND Articolisti_Incarichi.CategoriaID = 15

score 0 · Answer 4 · answered Aug 27 '13 at 07:31

I solved with this:

SELECT Articolisti_Articoli.* FROM Articolisti_Articoli JOIN Articolisti_Incarichi LEFT JOIN SitiWeb_Articoli ON SitiWeb_Articoli.ArticoloID = Articolisti_Articoli.ID WHERE SitiWeb_Articoli.ArticoloID IS NULL AND Articolisti_Articoli.IncaricoID = Articolisti_Incarichi.ID AND Articolisti_Incarichi.CategoriaID = 15

Error on JOIN mysql

4 Answers4

Linked