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')

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 quotes 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 quotes: that's why it could not be resolved.