CODEWITHSUNDEEP
mysqlsql
Manixman
Manixman

Reputation: 307

It is not displaying any results in a query

SELECT DISTINCT q.QuestionContent, o.OptionType, q.NoofAnswers, GROUP_CONCAT(DISTINCT Answer ORDER BY Answer SEPARATOR '') AS Answer, r.ReplyType, 
q.QuestionMarks
FROM Answer an 
INNER JOIN Question q ON q.QuestionId = an.QuestionId
JOIN Reply r ON q.ReplyId = r.ReplyId 
JOIN Option_Table o ON q.OptionId = o.OptionId

My query above is displaying an empty set of results even though it should display some results. It is suppose to display each distinct question (What I mean by this is that it should distinctly display rows so that there are no duplicate rows that contains exactly the same data across all columns) Can anyone fix the query so it outputs the results it should output? Below are the tables:

Session Table:

SessionId  SessonName
3          ANDES

Question Table:

QuestionId  SessionId   QuestionNo  QuestionContent       NoofAnswers   ReplyId     QuestionMarks   OptionId
1           3           1           What is 2+2?          1                 1           5               2
2           3           2           What is 2+2?          1                 1           5               2
3           3           3           Name these 2 flowers  2                 2           5               4

Answer Table:

AnswerId (Auto)  QuestionId  Answer
1                1           A
2                1           B
3                2           A
4                2           D
5                3           C

Reply Table:

ReplyId  ReplyType
1        Single
2        Multiple

Option Table:

OptionId OptionType
1        A-C
2        A-D
3        A-E
4        A-F

Upvotes: 0

Views: 73

Answers (1)

Taryn
Taryn

Reputation: 247670

I altered your query slightly and I am getting results. First, I changed to use a LEFT JOIN, then you will get questions returned if there is no answer, etc in the additional tables. Second, since you are using a aggregate function GROUP_CONCAT(), you need to use a GROUP BY:

SELECT
  q.QuestionContent, 
  o.OptionType, 
  q.NoofAnswers, 
  GROUP_CONCAT(DISTINCT Answer ORDER BY Answer SEPARATOR '') AS Answer, 
  r.ReplyType, 
  q.QuestionMarks
FROM Question q
LEFT JOIN Answer an
  ON q.QuestionId = an.QuestionId
LEFT JOIN Reply r 
  ON q.ReplyId = r.ReplyId 
LEFT JOIN Option_Table o 
  ON q.OptionId = o.OptionId 
group by q.QuestionContent

See SQL Fiddle with Demo

This returns the result:

|      QUESTIONCONTENT | OPTIONTYPE | NOOFANSWERS | ANSWER | REPLYTYPE | QUESTIONMARKS |
----------------------------------------------------------------------------------------
| Name these 2 flowers |        A-F |           2 |      C |  Multiple |             5 |
|         What is 2+2? |        A-D |           1 |    ABD |    Single |             5 |

Upvotes: 1

Related Questions