我正在尝试找出最有效的查询,将两个表中的数据放入一组结果中,并将用户数据左连接到其中。我需要帮助的语法查询。我把下面的东西放在一起,但不太对。
$query_chat = sprintf("SELECT comment_id, user_id, comment, timestamp FROM activity
UNION ALL
SELECT comment_id, user_id, comment, timestamp FROM comments
LEFT JOIN users.company, users.contact_person, users.email ON users.user_id = comments.user_id
WHERE user_id = %s
ORDER BY timestamp DESC", GetSQLValueString($_GET['comment_id'], "INT"));
语法是错误的,并抛出了以下内容-我试图通过只从users表中选择我需要的内容来提高效率。
1064 -你有一个错误的SQL语法;检查与MySQL服务器版本对应的手册,以获得使用near ' users的正确语法。contact_person,用户。电子邮件用户。User_id = comments。user_id WHERE us' at line 4
我现在已经到了下面这一点,但是在'field list'中得到未知列'users.company'
SELECT
comments.comment_id, comments.user_id, comments.comment,
comments.timestamp,
users.company, users.contact_person, users.email
FROM
comments
UNION ALL
SELECT
activity.comment_id, activity.user_id, activity.comment,
activity.timestamp,
users.company, users.contact_person, users.email
FROM
activity
LEFT JOIN users ON users.user_id = comments.user_id
WHERE
comments.comment_id = 69 ORDER BY timestamp ASC
你的问题是:
LEFT JOIN
users.company, users.contact_person, users.email
ON users.user_id = comments.user_id
语法为:
LEFT JOIN (table) ON (join-clause)
所以,就你的情况而言,我认为你需要这样的东西:
SELECT
comments.comment_id, comments.user_id, comments.comment,
comments.timestamp,
users.company, users.contact_person, users.email
FROM
comments
LEFT JOIN users ON users.user_id = comments.user_id
WHERE
users.user_id = %s
;
正如您所看到的,当使用JOIN
时,列首先出现(来自所有表),然后是表,以及它们相应的JOIN
子句。
Update:在回复你的更新时,你有两个选择。你可以这样做(你最接近):
SELECT
(users joined to activity)
UNION
(users joined to comments)
你已经知道如何做上面的(表达式)-参见我的答案的前面部分。或者,你可以这样做:
SELECT
*
FROM
(SELECT * FROM activity UNION SELECT * FROM comments) user_actions
LEFT JOIN users ON (users.user_id = user_actions.user_id)
请注意,在此更新的两个示例中,我提供了伪代码1 -您需要填写空白。你会发现,把一个一般化的例子(比如从一本手册中)应用到你自己的用例中,这种技能在计算机科学中经常发生,所以你应该尽可能多地练习。
1也就是说,包含子查询的第二个版本已经完成,只是更改了要获取的列—所以您可能已经完成了95%的工作!
于是终于有了下面这张图,感谢大家的指导
SELECT
comments.*, users.company, users.contact_person, users.email
FROM
comments
LEFT JOIN users ON users.user_id = comments.user_id
WHERE
comment_id = %s
UNION ALL
SELECT activity.*, users.company, users.contact_person, users.email
FROM
activity
LEFT JOIN users ON users.user_id = activity.user_id
WHERE
comment_id = %s
ORDER BY
timestamp ASC