Sql query to join four tables

Question

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.

tblquest

tblans

result

tblquest tblans result

What is the error you are getting? Syntax error? Or is it just not returning what you want? — Jacob Lambert, May 06 '15 at 05:47
syntax error... the right syntax to use near 'outer join SELECT DATE_FORMAT(c.Answer_Date,'%d %b %Y %T') as' at line 12 — Jadeer Parakalli, May 06 '15 at 05:51
when you join a subquery, the subquery needs to be in parenthesis, and be given an alias, and you're also missing the join predicates, and you cant use order by in the middle of a query like that. there's so much wrong with this query we pretty much need your schema, some sample data, and what you'd expect as output to have a chance of fixing it — pala_, May 06 '15 at 05:57
do you want the most recent answer only for each question, and then the five most recent questions with their most recent answer, or all the answers for each question, and limit to the five most recent answers? also, i'm not sure what that third table is -- all the info in it is in other tables, but the anstext is different? could you name those tables? — pala_, May 06 '15 at 06:39
first table- tblquest,second-tblans,last one is my result.i want most recent 5 question(if answer is there then append one most answer with the question if not give it as null) — Jadeer Parakalli, May 06 '15 at 06:46

score 2 · Accepted Answer · answered May 06 '15 at 06:57

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.

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.

score 1 · Answer 2 · edited May 23 '17 at 12:31

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

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

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

actually the first error is before that -- when `order by` is specified in the first subquery — pala_, May 06 '15 at 06:37

Sql query to join four tables

2 Answers2