CODEWITHSUNDEEP
mysqlsql
czmarc
czmarc

Reputation: 121

SQL query performance and Join tables

I am struggling to get the best query for my Q&A website. As a simplified example, I have 3 tables (questions, answers and users):

table_users

UserID  FirstName
1       John
2       Jack
3       Ana
4       Mary

table_questions

QuestionID  fkUserID Title
1           4        What is...?
2           2        Where is...?
3           1        How to...?
4           3        How much...?

table_answers

AnswerID    fkQuestionID    fkUserID  Answer
1           1               3         It is...
2           2               1         It is located in ZZ...
3           2               4         It is located in YY...
4           3               2         You have to...

I am creating a newsfeed to:

  1. Show the last answers ordered by the added date
  2. If the there are 2 answers for the same question, the newsfeed will only show the last answer (avoid duplication). In the example the newsfeed would only show the AnswerID 3 (It is located in YY...) and would skip the AnswerID 2.
  3. The newsfeed will show the first name of the person that created the question as well the person that answered it, both are located in the same table of users

So far, I created this query below, however:

I am having some performance/speed issues and I am not being able to only show the last answer of a question (issue 2), the DISTINCT select doesn't work as expected in this case.

SELECT 
DISTINCT (A.fkQuestionID) as QuestionID,
Q.Title,
A.AnswerID as AnswerID,
A.Answer,
U1.FirstName as AnswerFirstName,
Q.pkQuestionID,
U2.FirstName as QuestionFirstName

FROM table_answers as A
INNER JOIN table_questions as Q ON Q.QuestionID=A.fkQuestionID
INNER JOIN table_users as U1 ON U1.UserID=A.fkUserID
INNER JOIN table_users as U2 ON U2.UserID=Q.fkUserID
ORDER BY A.AnswerID DESC

I'd like to return the expected result as:

QuestionID  AnswerID  AnswerFirstName   QuestionFirstName
3           4         Jack              John
2           3         Mary              Jack
1           1         Ana               Mary

Any help would be very welcome. Thanks in advance!

Upvotes: 3

Views: 72

Answers (3)

Felix Pamittan
Felix Pamittan

Reputation: 31879

You can use MAX inside a subquery and do JOINs to get the needed columns:

DEMO

SELECT
    q.QuestionID,
    q.title,
    u1.FirstName AS Asker,
    a.AnswerID,
    a.answer,
    u2.FirstName AS Answerer
FROM table_questions q
INNER JOIN table_users u1
    ON u1.UserID = q.fkUserID
INNER JOIN (
    SELECT 
        fkQuestionID, MAX(AnswerID) AS AnswerID
    FROM table_answers
    GROUP BY fkQuestionID
) t
    ON t.fkQuestionID = q.QuestionID
INNER JOIN table_answers a
    ON a.AnswerID = t.AnswerID
    AND a.fkQuestionID = t.fkQuestionID
INNER JOIN table_users u2
    ON u2.UserID = a.fkUserID

Replace the INNER JOIN with LEFT JOIN if you wish to include questions without answers.

Upvotes: 2

ughai
ughai

Reputation: 9880

You can group by fkQuestionID to get the max(AnswerID) and then use it in further join like this.

SELECT 
q.QuestionID,
q.Title,
a1.AnswerID as AnswerID,
a1.Answer,
U1.FirstName as AnswerFirstName,
q.pkQuestionID,
U2.FirstName as QuestionFirstName
FROM
(SELECT MAX(AnswerID) as AnswerID,fkQuestionID FROM table_answers GROUP BY fkQuestionID ) as a2
INNER JOIN table_answers a1  ON a1.fkQuestionID = a2.fkQuestionID AND a1.AnswerID = a2.AnswerID
INNER JOIN table_questions q ON q.QuestionID = a2.fkQuestionID 
INNER JOIN table_users as U1 ON U1.UserID=a1.fkUserID
INNER JOIN table_users as U2 ON U2.UserID=q.fkUserID
ORDER BY a2.AnswerID DESC

Upvotes: 1

Abdul Rasheed
Abdul Rasheed

Reputation: 6709

Plz try like this 

SELECT  QuestionID ,fkUserID,title,ANS.Answer
FROM    table_questions QST
INNER JOIN (SELECT fkQuestionID,MAX(AnswerID) AnswerID
            FROM    table_answers
            GROUP BY fkQuestionID) ANS ON ANS.fkQuestionID = QST.QuestionID
INNER JOIN table_answers LANS ON LANS.AnswerID = ANS.AnswerID

Upvotes: 0

Related Questions