CODEWITHSUNDEEP
phpmysql
Hobbyist
Hobbyist

Reputation: 16202

MySQL Query returning strange values

The query is supposed to do the following:

Obtain the question and associated answers based on the identification number of the question. (In this case the identification number is called account_id. Order them so that each question (and it's appropriate answers) are lined up in order.

The query:

SELECT * 
  FROM quiz_questions q
 JOIN quiz_answers a
    ON q.account_id = a.account_id
WHERE q.account_id = 1840979156127491
ORDER BY q.question_id
ASC LIMIT 5

The quiz_questions table and contents: enter image description here

The quiz_answers table and contents: enter image description here

Strange altered results:enter image description here

As you can see where the question_id is ordered, all of the values are 1 however, the other question_id field (These fields match if you look at the previous data) are representing two, like they should. Also, even though the question (with the id of 2) is displayed in the secondary question_id field, the text for the question still belongs to question 1: (do you like waffles) apposed to (do you like pancakes).

What is causing these results? Here's what the expected results should look like: (Ignoring duplicate fields).

+------------------+-------------+----------------------+-----------+----------------------+---------+
|    account_id    | question_id |       question       | answer_id |        answer        | correct |
+------------------+-------------+----------------------+-----------+----------------------+---------+
| 1840979156127491 |           1 | Do you like waffles  |         1 | Yes i like waffles   |       1 |
| 1840979156127491 |           1 | Do you like waffles  |         5 | I kinda like waffles |       1 |
| 1840979156127491 |           1 | Do you like waffles  |         6 | Not at all           |       0 |
| 1840979156127491 |           2 | Do you like pancakes |         7 | Yes                  |       1 |
| 1840979156127491 |           2 | Do you like pancakes |         8 | No           |       0 |
+------------------+-------------+----------------------+-----------+----------------------+---------+

Upvotes: 0

Views: 73

Answers (1)

Rick Su
Rick Su

Reputation: 16440

You need to join by account_id and also question_id

SELECT * FROM `quiz_questions`
INNER JOIN `quiz_answers` 
ON `quiz_questions`.`account_id` = `quiz_answers`.`account_id`
  AND `quiz_questions`.`question_id` = `quiz_answers`.`question_id`
WHERE `quiz_questions`.`account_id` = '1840979156127491'
ORDER BY `quiz_questions`.`question_id`
ASC LIMIT 5

Upvotes: 2

Related Questions