MYSQL JOIN with AND error

Question

我正在尝试从名称表中提取first_name，但前提是该users_id位于订单表中并且order_num = 1，但我不断收到错误。

SELECT first_name
FROM customers
LEFT JOIN orders
ON orders.order_id = customers.cust_id 
AND orders.order_num = 1

但是我的查询返回多个值而不仅仅是一个值，就像它忽略orders.order_num=1一样 - 我的查询看起来正确，还是我做错了什么？

Answer 1

SELECT first_name
FROM customers
LEFT JOIN orders
ON orders.order_id = customers.cust_id 
WHERE orders.order_num = 1

Answer 2

AND orders.order_num = 1

应该是

WHERE orders.order_num = 1

Answer 3

试试这个。

SELECT first_name
FROM customers
INNER JOIN orders
ON (orders.cust_id = customers.id)
WHERE orders.order_num = 1
GROUP BY customers.id

INNER JOIN将仅包含ON条件匹配的记录

WHERE将结果限制为仅在 order_num = 1 时

GROUP BY确保您每个客户只获得 1 个结果