MySQL 5 left join unknown column

Question

I had the below query working in mysql 4.1, but does not in 5.0:

SELECT * FROM email e, event_email ee 
LEFT JOIN member m on m.email=e.email 
WHERE ee.email_id = e.email_id

The error: 1054 (Unknown column 'e.email' in 'on clause')

Archbishopric

Answer 1

You can only refer the tables previously joined with the JOIN clause in the ON clause.

SELECT  *
FROM    email e
JOIN    event_email ee 
ON      ee.email_id = e.email_id
LEFT JOIN
        member m
ON      m.email = e.email 

This can be illustrated better if I put the parentheses around the ANSI JOINS in your original query:

SELECT  *
FROM    email e,
        (
        event_email ee
        LEFT JOIN
                member m
        ON      m.email = e.email 
        )
WHERE   ee.email_id = e.email_id

As you can see, there is no source for e.email inside the parentheses: that's why it could not be resolved.

Gracye

By quotes, do you mean parentheses? In the first query, I don't understand your explanation. Which on clause are you referring to? Can you re-phrase? Thanks – Archbishopric

Answer 2

From MySQL documentation page

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.

Example:

CREATE TABLE t1 (i1 INT, j1 INT);
CREATE TABLE t2 (i2 INT, j2 INT);
CREATE TABLE t3 (i3 INT, j3 INT);

SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);

Previously, the SELECT was legal due to the implicit grouping of t1,t2 as (t1,t2). Now the JOIN takes precedence, so the operands for the ON clause are t2 and t3. Because t1.i1 is not a column in either of the operands, the result is an Unknown column 't1.i1' in 'on clause' error. To allow the join to be processed, group the first two tables explicitly with parentheses so that the operands for the ON clause are (t1,t2) and t3:

SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);

Alternatively, avoid the use of the comma operator and use JOIN instead:

SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);

This change also applies to statements that mix the comma operator with INNER JOIN, CROSS JOIN, LEFT JOIN, and RIGHT JOIN, all of which now have higher precedence than the comma operator.

Biting

Absolutely correct, thanks! My query was suddenly broken after adding a new table to the FROM clause, and the parentheses solution worked perfectly! this seems as a very annoying change in MySQL, but I'm sure they had a good reason for it. – Xylotomy

Answer 3

The answer would be to place the JOIN statement directly after the table in the FROM statement, because that is the table you are joining on:

SELECT * 
FROM email e 
LEFT JOIN member m on m.email=e.email, 
event_email ee 
WHERE ee.email_id = e.email_id

Icelander

Thank you. This worked for me and was much easier that rewriting the entire query with an additional 'JOIN ON'. – Memorialize

Yep, this turns out to be a better simple solution to apply – Debauchee