mysql SELECT FROM 两个表和 LEFT JOIN

Question

我在从两个表中选择并左联接到第三个表时遇到问题。查询:

SELECT 
    c.id AS currency, u.user, us.enabled AS currency 
FROM 
    users AS u, 
    currency AS c
    LEFT JOIN users_settings AS us ON(c.id=us.currency, u.user=us.user)
WHERE 
    c.off=0 AND c.disabled=0 AND c.status=1 

错误:

#1054 - Unknown column 'u.user' in 'field list'

我需要用户和货币的笛卡尔积。用户:id、登录名、邮箱、密码

http://en.wikipedia.org/wiki/Cartesian_product

Answer 1

您正在混合使用显式和隐式连接语法。试试这个:

SELECT c.id AS currency, u.user, us.enabled AS currency
FROM currency AS c LEFT JOIN
     users_settings AS us
     ON c.id = us.currency LEFT JOIN
     users u
     on u.user = us.user
WHERE c.off = 0 AND c.disabled = 0 AND c.status = 1 ;

MySQL 不一定允许通过 , 识别字段。作用域是、 和cross join 之间的区别之一。但是，在任何情况下，您都应该为每个 join 使用单独的 on 子句。

这是documentation中的描述:

INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table).

However, the precedence of the comma operator is less than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur. Information about dealing with this problem is given later in this section.

与其试图真正理解这意味着什么，不如避免在 from 子句中使用 ,。它永远不需要。