左连接表与其他两个表的结果的 MySQL 查询问题

Question

我正在尝试从一个表中选择数据，该数据来自连接两个不同表的数据结果，但出现错误，我想不通:

SELECT CONCAT(firstname,lastname) AS staffname
FROM `cms_users`
RIGHT JOIN
(SELECT DISTINCT CONCAT(a.firstname," ",a.lastname) AS clientname, b.doa
FROM `cms_clients` a
INNER JOIN `cms_question_report` b
ON a.id = b.cid
WHERE b.doa < '2015-04-15 23:00:00' AND b.doa > '2015-04-09 00:00:00') incidents

我得到的错误是:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 30' at line 9

但我的查询中甚至没有 LIMIT 0,30。

Answer 1

您的查询不完整。您缺少正确连接的连接列。请参见下文，但您必须将 PRIMARY_KEY 替换为 cms_users 表的主键。还要确保 i.id 是两个表的正确连接列。

SELECT CONCAT(firstname,lastname) AS staffname
FROM `cms_users`
RIGHT JOIN
(SELECT DISTINCT CONCAT(a.firstname," ",a.lastname) AS clientname, b.doa
FROM `cms_clients` a
INNER JOIN `cms_question_report` b
ON a.id = b.cid
WHERE b.doa < '2015-04-15 23:00:00' AND b.doa > '2015-04-09 00:00:00')  
AS i ON i.id = cms_users.PRIMARY_KEY

希望对您有所帮助!