Select with multiple conditions from a single user

Question

I want to do a search of all the users who have taken the same exams as me in a database (MySql).

The structure of the database is id, user_id, exam_id and date

Well, I have done the exams with the id 5,8,9, I want to know how many have done the same as me (For example, other users who have taken the 5,7,9 exams would not count, only users who 5,8,9)

If someone has exams 4, 5, 8 and 9 and I have 4,8,9 it would count because they coincide that we have done the pampering.

It will not matter if other users have more

cant it be simple `exam_id IN (5,8,9)` ? Or i am missing something in the question? — Koushik Roy, Dec 09 '20 at 17:30
If someone has exams 4, 5, 8 and 9 and I have 4,8,9 it would count because they coincide that we have done the pampering. It will not matter if other users have more (sorry for having made it clearer before) — Gorka Rodriguez, Dec 09 '20 at 18:07

GMB · Answer 1 · 2020-12-09T17:35:22.247

You can use some relational division technique:

select e.user_id
from exams e
inner join exams e1 on e1.exam_id = e.exam_id and e1.user_id <> e.user_id
where e1.user_id = ?
group by e.user_id
having count(*) = (select count(*) from exams where user_id = ?)

This brings users that have taken all exams that user ? took. If you want an exact match (that is, filter out users that have taken more tests):

select e.user_id
from exams e
left join exams e1 
    on  e1.exam_id = e.exam_id 
    and e1.user_id <> e.user_id
    and e1.user_id = ?
group by e.user_id
having count(e1.user_id) = (select count(*) from exams where user_id = ?)

If you don't have too many exams per users, you could also use string aggregation:

select user_id
from exams 
where user_id <> ?
group by user_id
having group_concat(exam_id order by exam_id) = (
    select group_concat(exam_id order by exam_id) from exams where user_id = ?
)

Select with multiple conditions from a single user

1 Answers1