MySQL 5 left join unknown column

mysql left-join mysql-error-1054
Neil picture Neil · May 12, 2010 · Viewed 18.4k times · Source

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

Quassnoi picture Quassnoi · May 12, 2010

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.

Related questions

How to limit results of a LEFT JOIN

Take the case of two tables: tbl_product and tbl_transaction. tbl_product lists product details including names and ids while tbl_transaction lists transactions involving the products and includes dates, product-ids, customers etc. I need to display a web-page …

Read More
Insert using LEFT JOIN and INNER JOIN

Hey all i am trying to figure out how to go about inserting a new record using the following query: SELECT user.id, user.name, user.username, user.email, IF(user.opted_in = 0, 'NO', 'YES') AS optedIn FROM user LEFT …

Read More
LEFT JOIN only first row

I read many threads about getting only the first row of a left join, but, for some reason, this does not work for me. Here is my structure (simplified of course) Feeds id | title | content ---------------------- 1 | Feed 1 | ... Artists artist_id | …

Read More