Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

Calid's avatar
Level 3

Query with orWhere only on same foreign key

I have a tables (answers, questions, quizzes) with foreign key for questions table (question_id). what I want is to look for questions where answer is wrong ( right = 2) orWhere if that same question_id have a correct answer (right = 1)

+----+---------+-------------+-------+--------+---------------------+---------------------+
| id | quiz_id | question_id | right | answer | created_at          | updated_at          |
+----+---------+-------------+-------+--------+---------------------+---------------------+
| 1  | 11      | 589         | 1     | a      | 2019-03-03 15:06:47 | 2019-03-03 15:06:47 |
+----+---------+-------------+-------+--------+---------------------+---------------------+
| 2  | 11      | 590         | 2     | b      | 2019-03-03 15:06:47 | 2019-03-03 15:06:47 |
+----+---------+-------------+-------+--------+---------------------+---------------------+
| 3  | 11      | 591         | 1     | d      | 2019-03-03 15:06:47 | 2019-03-03 15:06:47 |
+----+---------+-------------+-------+--------+---------------------+---------------------+
| 4  | 12      | 601         | 1     | d      | 2019-04-09 15:06:47 | 2019-04-09 15:06:47 |
+----+---------+-------------+-------+--------+---------------------+---------------------+
| 5  | 12      | 602         | 2     | d      | 2019-03-03 15:06:47 | 2019-04-09 15:06:47 |
+----+---------+-------------+-------+--------+---------------------+---------------------+
| 6  | 12      | 590         | 1     | c      | 2019-03-03 15:06:47 | 2019-04-09 15:06:47 |
+----+---------+-------------+-------+--------+---------------------+---------------------+

I should get the following result showing only correct ( right = 1) for question_id : 590

+----+---------+-------------+-------+--------+---------------------+---------------------+
| id | quiz_id | question_id | right | answer | created_at          | updated_at          |
+----+---------+-------------+-------+--------+---------------------+---------------------+
| 2  | 11      | 590         | 2     | b      | 2019-03-03 15:06:47 | 2019-03-03 15:06:47 |
+----+---------+-------------+-------+--------+---------------------+---------------------+
| 5  | 12      | 602         | 2     | d      | 2019-03-03 15:06:47 | 2019-04-09 15:06:47 |
+----+---------+-------------+-------+--------+---------------------+---------------------+
| 6  | 12      | 590         | 1     | c      | 2019-03-03 15:06:47 | 2019-04-09 15:06:47 |
+----+---------+-------------+-------+--------+---------------------+---------------------+

The goal is to get all questions that have a wrong answer and check if that same question has any correct answer in later quiz attempt to remove it from the wrong-answer list, after getting results I am simply loop over the results and check if the question have any later correct answer using 'updated_at'

0 likes
1 reply
jlrdw's avatar

You might want to use a 0 or 1. 0 = wrong, false. 1 = correct, true.

You could just index the field and order by "id and "right". Would probably be okay not to index, unless the table is going to be huge.

Please or to participate in this conversation.