CODEWITHSUNDEEP
mysql
Medo Medo
Medo Medo

Reputation: 1018

mysql select query on two tables in specific order

I have two tables
questions table like below
questions content and answers table like below
enter image description here
every question has multiple answers
How can I make query to return the result as below table
enter image description here

Upvotes: 2

Views: 114

Answers (2)

Grisha Weintraub
Grisha Weintraub

Reputation: 7986

You have to order by two columns - q_id and ans_id. Since in question_tb there is no ans_id field you can put 0 instead.

select t.id, t.q_content from
(
  select q_id, q_content, 0 k, q_id id from question_tb
  union 
  select ans_q_id, ans_content, ans_id, ans_id from answer_tb
) t  order by t.q_id, t.k

Upvotes: 3

Curt
Curt

Reputation: 5722

Hmmm.. I think you need to re-think your structure a bit: the last table (or query result) needs a column to designate which is the question, and which are the answers, and also to indicate the correct answer.

Also, I assume that each set of questions (and answers) will be tied to a particular user, so you will need a user_key in the answer table, as well. 

 SELECT Q.q_content          AS question,
        ANS.ans_content      AS answer,
        ANS.is_correct,
        ANS.user_id,
   FROM Question_TB Q
        INNER JOIN tb_answer ANS
          ON ANS.ans_q_id = Q.q_id
        ORDER BY ANS.user_id, Q.q_id

Upvotes: -1

Related Questions