CODEWITHSUNDEEP
mysqlsql
user3627265
user3627265

Reputation: 191

SQL retrieving data from 2 different table

I have these two table namely question and answer.

Question table

exam_id /* unique ID
ref_number /* for numbering of question */
value /* the question */

Answer table

    exam_id, /*id to connect to question */
    ref_number /*identifier for what question */
    answer /*the value */

I used this SQL to get the fields in the database 

SELECT exam_answer.*, exam_question.* FROM exam_question INNER JOIN exam_answer ON exam_question.exam_id = exam_answer.exam_id WHERE exam_question.exam_id =10

I tested using the ID 10 to get the fields, but the questions are repeating based on the number of counts of answers. Which is wrong, what I'm trying to do is to query the question and then the corresponding answer. Something like this. 

Question 1
Answer 1,
answer 2,
answer 3
answer 4

Question 2
Answer 1,
answer 2,
answer 3
answer 4

Any idea on what I am missing?

Upvotes: 0

Views: 56

Answers (2)

sgeddes
sgeddes

Reputation: 62831

While this is generally to be considered presentation logic, I have had the need to handle this using the database. Using union and creating a sort order can handle the layout.

select result
from (
  select exam_id, ref_number, value as result, 1 as sort_order
  from question 
  union all
  select exam_id, ref_number, answer as result, 2 as sort_order
  from answer
 ) t
order by exam_id, ref_number, sort_order

Upvotes: 1

spencer7593
spencer7593

Reputation: 108370

It looks like your are missing a predicate in the ON clause

 ON exam_question.exam_id = exam_answer.exam_id 
AND exam_question.ref_number = exam_answer.ref_number

Based on the information given in your question, it looks like you want ref_number column on the answer to match the ref_number column on the question.

Upvotes: 0

Related Questions