连接4个表的Sql查询。

SELECt 
    qst_id,qst_title,ans_date,ans_text 
FROM
    (
        SELECT 
            a.Question_Id as qst_id,a.Question_Title as qst_title,a.Question_Text as qst_text,DATE_FORMAT(a.LastActivity_Date,'%d %b %Y %T') as qst_date,b.UserForum_Image as qst_prof,b.ScreenName as qst_scname

        FROM 
            tblforumquestion a, tblregistration2_2 b 
        WHERE  a.RegistrationId=b.RegistrationId and a.LastActivity_Date between '2014-0-01 00:00:00' and '2015-05-01 00:00:00'
        ORDER BY a.LastActivity_Date desc limit 5

        outer join

        SELECT 
            DATE_FORMAT(c.Answer_Date,'%d %b %Y %T')  as ans_date,c.Answer_Text as ans_text,d.UserForum_Image as ans_prof,d.ScreenName as ans_scname
        FROM 
            tblforumanswer c ,tblregistration2_2 d
        where c.Answer_Id in 
            ( 
                SELECT  MAX(Answer_Id)
                FROM tblforumanswer
                GROUP BY Question_Id 
            )  
        and c.RegistrationId=d.RegistrationId 
        order by c.Answer_Date desc limit 5
    )

I am trying to get latest 5 question and answers from my post.if any question without answer is there,it should also display as question details in one row with null answer details.But the above code is getting error.Any help is appreciable.my database is mysql.

我正在努力从我的帖子中得到最新的5个问题和答案。如果有任何没有答案的问题，它也应该以问题细节的形式显示在一行中，其中包含空答案细节。但是上面的代码有错误。任何帮助都是明显的。我的数据库是mysql。

tblquest tblans result

tblquest tblans结果

2 个解决方案

#1

I think we've finally extracted enough detail to arrive at an answer:

我想我们终于找到了足够的细节来回答这个问题:

select q.qstid, q.qsttext, a.anstext
  from tblquest q
    left join tblans a
      on q.qstid = a.qstid
    left join tblans a2
      on a.qstid = a2.qstid and a.ansdate < a2.ansdate
  where a2.ansdate is null
  order by q.qdate desc limit 5;

demo here

演示

We left join the answers to the questions, in order to ensure we have keep all questions, including those without answers.

为了确保所有的问题，包括那些没有答案的问题，我们都留下了答案。

We then left join to the answers again, but this time on a range condition in order to just pick off the most recent answer to the question. If there is no a2 with a date greater than a, then that a must be the most recent answer - this is filtered for by the where a2.ansdate is null clause.

然后我们又把join放在了答案中，但是这次是在一个范围条件下，目的是为了得到这个问题的最新答案。如果没有日期大于a的a2，那么a一定是最近的答案——这被a2的where过滤了。ansdate是无效条款。

That could also be accomplished with a subquery if you preferred.

如果您愿意，也可以使用子查询来完成。

Finally, we just order and limit our results in order to get the 5 most recent questions.

最后，我们只对结果进行排序和限制，以得到最近的5个问题。

#2

Problem with your outer join syntax. Check the comment and sample data.

外部连接语法的问题。检查注释和示例数据。

SELECT
    qst_id,qst_title,ans_date,ans_text 
FROM
    (
        SELECT 
            a.Question_Id as qst_id,a.Question_Title as qst_title,a.Question_Text as qst_text,DATE_FORMAT(a.LastActivity_Date,'%d %b %Y %T') as qst_date,b.UserForum_Image as qst_prof,b.ScreenName as qst_scname

        FROM 
            tblforumquestion a, tblregistration2_2 b 
        WHERE  a.RegistrationId=b.RegistrationId and a.LastActivity_Date between '2014-0-01 00:00:00' and '2015-05-01 00:00:00'
        ORDER BY a.LastActivity_Date desc limit 5

        outer join  --Error comes here

        SELECT 
            DATE_FORMAT(c.Answer_Date,'%d %b %Y %T')  as ans_date,c.Answer_Text as ans_text,d.UserForum_Image as ans_prof,d.ScreenName as ans_scname
        FROM 
            tblforumanswer c ,tblregistration2_2 d
        where c.Answer_Id in 
            ( 
                SELECT  MAX(Answer_Id)
                FROM tblforumanswer
                GROUP BY Question_Id 
            )  
        and c.RegistrationId=d.RegistrationId 
        order by c.Answer_Date desc limit 5
    )

--This is example of outer join
SELECT 
    A.*, B.*
FROM 
    TableA a outer join TableB b on a.RegistrationId = b.RegistrationId

Refer link for more detail:

详情请参阅连结:

Full Outer Join in MySQL

完全外连接到MySQL

https://dev.mysql.com/doc/refman/5.0/en/outer-join-simplification.html

https://dev.mysql.com/doc/refman/5.0/en/outer-join-simplification.html

http://www.w3schools.com/sql/sql_join_full.asp

http://www.w3schools.com/sql/sql_join_full.asp