Why are 2 left joins in one query causing an error?

Note: If you're looking for a solution to your own problem and you were drawn here, it will probably not help you. Unless you're analyzing one SQL query for the bug when in fact the bug might exist in a query that follows it, unbeknownst to you, this question can be of no help to you. I'm just warning you now, because none of the data provided in this question actually leads to the answer.

In o开发者_StackOverflowrder to extend a database table regular_rules without modifying the table itself, I have created an additional table (extended_rules) whose PK is also a FK to regular_rules. I can then place any new columns in extended_rules, then any time I load up a record from it I just need to join it with regular_rules in order to treat it as though it's the full object. Thus, as you can see, these two tables share a one-to-one relationship.

However, regular_rules also has a one-to-many relationship with another table (rule_coupons), which also needs to be joined.

Thus, I have the following query:

SELECT `main_table`.*, `primary_coupon`.`code`, `regular_rules`.*
FROM `extended_rules` AS `main_table`
LEFT JOIN `rule_coupons` AS `primary_coupon`
    ON main_table.rule_id = primary_coupon.rule_id AND primary_coupon.is_primary = 1
LEFT JOIN `regular_rules`
    ON `main_table`.`rule_id` = `regular_rules`.`rule_id`

This looks perfectly fine to me. However, I receive the following error message:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'main_table.rule_id' in 'on clause'

Through testing, I have found that this error is occurring on the second join, despite that I've successfully accessed main_table.rule_id just moments prior in the very same query. Interestingly, if I swap the two joins, the error still occurs on the second one, which leads me to believe it may not be a logic error but rather a syntax one, pertaining to something that I simply don't understand yet.

Please note that I do not have the luxury of modifying the first join (onto rule_coupons), and that I may only modify the second join (onto regular_rules) or add new parts to the query.

Edit: Interesting development... If I paste the query into phpMyAdmin and execute it there, it works fine. I also tossed together a quick, basic PHP script to execute the query using mysqli, and that worked fine. So far, it seems to only happen within the scope of the platform upon which I'm building (Magento). I've been working with Magento for a long time and I've never come across a strange database issue like this before... So I'm still not sure what's wrong, but at least now you have a bit more context.

Just a guess: Try to omit the table alias and set brackets around the ON clauses:

SELECT `extended_rules`.*, `rule_coupons`.`code`, `regular_rules`.*
FROM `extended_rules`
LEFT JOIN `rule_coupons`
    ON (extended_rules.rule_id = rule_coupons.rule_id AND rule_coupons.is_primary = 1)
LEFT JOIN `regular_rules`
    ON (`extended_rules`.`rule_id` = `regular_rules`.`rule_id`)

If this should work, then it is a bug I have observed several times: MySQL seems to have a problem if you're using aliases for some tables but not for all. Otherwise, just ignore what I've written ;-)

As it turns out, I had tracked this problem down to the wrong spot in the code.

As you can see, the SQL is intended to return a collection of results, as the purpose of that query is to populate a grid. The error occurred whenever I tried to load that grid. Unfortunately, I was unaware that someone else was performing another query for each row that was returned by my query.

Once I realized this, I looked into that second query and therein was the source of the problem. This also explains why it appeared as though the error always occurred on the "second" join in the query, no matter what it was... the error was simply occurring after the query I was analyzing!

All of this is to say, the information I provided in the question could not possibly lead to the answer, as I was incorrect to start with. I apologize to anyone who tried to figure out the answer and wasted their time.