How can I perform a one-to-many select in mysql and avoid nested for loops when querying?

Question

I am creating a quiz web app. I have two tables: one table with questions, the other with answers. There is one question to many answers, and there are many answers to one question. The length of the answers may vary. For example, one question may have 2 answers, while another may have 5 possible answers.

Here is a silly example:

Questions table:

 +-------------+-----------------------------+------------------+
 |    id       |           question          |  correct_answer  |
 +-------------+-----------------------------+------------------+
 |    1        |  What is my name?           |      2           |
 +-------------+-----------------------------+------------------+
 |    2        |  'Green' is a color.        |      1           |
 +-------------+-----------------------------+------------------+

Answers table:

 +-------------+---------------+-----------------+---------------+
 |    id       |  question_id  |   answer        |  answer_num   |
 +-------------+---------------+-----------------+---------------+
 |    1        |      1        |   Tom           |      1        |
 +-------------+---------------+-----------------+---------------+
 |    2        |      1        |   Hayden        |      2        |
 +-------------+---------------+-----------------+---------------+
 |    3        |      1        |   Chris         |      3        |
 +-------------+---------------+-----------------+---------------+
 |    4        |      1        |   Gale          |      4        |
 +-------------+---------------+-----------------+---------------+
 |    5        |      2        |   true          |      1        |
 +-------------+---------------+-----------------+---------------+
 |    6        |      2        |   false         |      2        |
 +-------------+---------------+-----------------+---------------+

I am wanting to select the questions and possible answers to that question in one query, versus making multiple queries, especially nested ones in a loop, which is bad.

@metal I tried to perform a group by aggregation and the following error occurred: ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.answers.answer' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by — Hayden, Sep 02 '19 at 02:18
@metal SELECT questions.id, questions.question, answers.answer FROM questions JOIN answers ON questions.id = answers.question_id GROUP by questions.id; — Hayden, Sep 02 '19 at 02:20
@metal I want to be able to loop through them in nodejs and aggregate the answers together — Hayden, Sep 02 '19 at 02:42
simple join would be able to get all answers and questions, and on you node JS, you can group them via question id. — Ed Bangga, Sep 02 '19 at 02:44
Please clarify via edits, not comments. Please in code questions give a [mre]--cut & paste & runnable code; example input (tabular initialization code) with desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. That includes the least code you can give that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) PS Time to read a published intro to using SQL. PS [Is there any rule of thumb to construct SQL query from a human-readable description?](https://stackoverflow.com/a/33952141/3404097) — philipxy, Sep 02 '19 at 03:19
Constraints (including N:M:... cardinalities) do not need to be known to query. The presence of constraints just means that more queries return the same answer as the queries that can be written not knowing or not having constraints, where otherwise they wouldn't. — philipxy, Sep 10 '19 at 03:21

score 1 · Answer 1 · answered Sep 02 '19 at 02:30

1

You can use sql join for this requirement

select * from answers a
join question q on q.id = a.question_id

answered Sep 02 '19 at 02:30

Ed Bangga

12,879
4
16
30

score 0 · Answer 2 · edited Sep 10 '19 at 02:44

0

Select 
q.id qid, question,correct_answer,a.id aid,answer,answer_num
from Question q,Answers a
where q.id=a.question_id

edited Sep 10 '19 at 02:44

Dale K

25,246
15
42
71

answered Sep 02 '19 at 02:21

yu2

126
4

How can I perform a one-to-many select in mysql and avoid nested for loops when querying?

2 Answers2