使用Left Join和Where IN子句的MYSQL查询

I have three tables A B C and i'm trying to retrieve information from all three.

我有三个表A B C,我正在尝试从这三个表中检索信息。

A has the columnns userid avatar username and B has the column postid, dateshared and C has the column commenter postid datecommented.

A具有columnns userid avatar用户名,B具有postid列,dateshared和C具有postid datecommented列commenter。

I'm trying to run the query

我正在尝试运行查询

Select  C.comment, C.commenter,  C.datecommented, B.postid, B.dateshared A.username A.avatar from B Left Join C  Left join A on C.postid = B.postid AND A.userid = C.commenter where B.postid IN ('1','2','3') order by  C.dateshared desc

but it gives the following error:

但它给出以下错误:

 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 'where B.postid IN ('1', '2', '3') order by C.dateshared '

Can anyone point out what I'm doing wrong or suggest how to go about it?

任何人都可以指出我做错了什么或建议如何去做?

3 个解决方案

#1

Each LEFT JOIN requires its own ON condition:

每个LEFT JOIN都需要自己的ON条件:

SELECT  C.comment, C.commenter, C.datecommented, B.postid, B.dateshared, A.username A.avatar
FROM    B
LEFT JOIN
        C
ON      C.postid = B.postid
LEFT JOIN
        A
ON      A.userid = C.commenter
WHERE   B.postid IN ('1','2','3')
ORDER BY
        C.dateshared desc

#2

I see a couple of issues with your example query:

我看到了您的示例查询的几个问题:

You're missing some commas in the first part of your query

您在查询的第一部分中遗漏了一些逗号

You aren't specifying any join criteria for your join on C

您没有为C上的联接指定任何加入条件

I would rewrite this to be something like:

我会把它重写为:

    SELECT C.comment, C.commenter, C.datecommented, B.postid, B.dateshared, A.username, A.avatar 
    FROM B
    LEFT JOIN C ON C.postid = B.postid
    LEFT JOIN A ON A.userid = C.commenter
    WHERE B.postid IN ('1','2','3') 
    ORDER BY C.dateshared desc

#3

This should work for you, your query had some syntax errors:

这应该适合您,您的查询有一些语法错误:

Select  C.comment,C.commenter,C.datecommented,B.postid,B.dateshared,A.username,A.avatar
from B
Left Join C on C.postid = B.postid
Left join A on A.userid = C.commenter
where B.postid IN ('1','2','3')
order by  C.dateshared desc

#1