How can this SQL be wrong? What am I not seeing?

Question

Can anybody please spot my error, this should be a legal query in SQL shouldn't it??

Unknown column u.usr_auto_key in the ON clause

This is the database schema:

User: (usr_auto_key, name, etc...)
Setting: (set_auto_key, name etc..)
User_Setting: (usr_auto_key, set_auto_key, value)

And this is the query...

        SELECT 
        `u`.`usr_auto_key` AS `u__usr_auto_key`, 
        `s`.`set_auto_key` AS `s__set_auto_key`, 
        `u2`.`usr_auto_key` AS `u2__usr_auto_key`, 
        `u2`.`set_auto_key` AS `u2__set_auto_key`, 
        `u2`.`value` AS `u2__value` 
        FROM `User` `u`, `Setting` `s` 
        LEFT JOIN `User_Setting` `u2` ON `u`.`usr_auto_key` = `u2`.`usr_auto_key` 
        WHERE (`s`.`sct_auto_key` = 1 AND `u`.`usr_auto_key` = 1 AND admin_property is null)

Answer 1

Try switching User and Settings in the from clause:

SELECT 
        `u`.`usr_auto_key` AS `u__usr_auto_key`, 
        `s`.`set_auto_key` AS `s__set_auto_key`, 
        `u2`.`usr_auto_key` AS `u2__usr_auto_key`, 
        `u2`.`set_auto_key` AS `u2__set_auto_key`, 
        `u2`.`value` AS `u2__value` 
        FROM `Setting` `s`, `Users` `u`
        LEFT JOIN `User_Setting` `u2` ON `u`.`usr_auto_key` = `u2`.`usr_auto_key` 
        WHERE (`s`.`sct_auto_key` = 1 AND `u`.`usr_auto_key` = 1 AND admin_property is null)

Answer 2

Don't mix SQL-89 "comma-style" join syntax with SQL-92 JOIN syntax. There are subtle issues with the precedence of these two types of join operations.

In your case, the consequence is that it's evaluating the join condition LEFT JOIN before the u table alias exists. That's why it doesn't know what u.usr_auto_key is.

You can correct this problem by using JOIN syntax for all joins:

SELECT 
  `u`.`usr_auto_key` AS `u__usr_auto_key`, 
  `s`.`set_auto_key` AS `s__set_auto_key`, 
  `u2`.`usr_auto_key` AS `u2__usr_auto_key`, 
  `u2`.`set_auto_key` AS `u2__set_auto_key`, 
  `u2`.`value` AS `u2__value` 
FROM `User` `u` JOIN `Setting` `s`
LEFT JOIN `User_Setting` `u2` ON `u`.`usr_auto_key` = `u2`.`usr_auto_key` 
WHERE (`s`.`sct_auto_key` = 1 AND `u`.`usr_auto_key` = 1 AND admin_property is null)

I didn't see any join condition between u and s in your query, so I assume you intend this to be a Cartesian product?

---

For more details on the interaction between the two syntax forms for join, see the section Join Processing Changes in MySQL 5.0.12 on the page http://dev.mysql.com/doc/refman/5.0/en/join.html

---

Re your comment: As I said, it has to do with operator precedence. If you have a SQL query with FROM A, B JOIN C then it evaluates the B JOIN C before it pays any attention to A -- that includes assigning table aliases. So if your join condition for B JOIN C uses the table alias for A you get an error because that alias doesn't exist yet.

If you reverse it and run B, A JOIN C then as it evaluates the join condition for A JOIN C the alias for A is available and it works (in this case at least).

But this is a fragile solution, because you might also need a query that can't be fixed just by reordering A and B. It's better to just stop using the outdated join syntax with commas. Then any join expression has access to all your table aliases and you'll never have this problem in any query.