CODEWITHSUNDEEP
sqloracle-database
user3756741
user3756741

Reputation: 109

SQL get all qualified users

For some reason I am really struggling with getting this query right. Maybe there is a concept I dont get. I have a table that has records for courses taken. So you can think of it like course_id, username, date_completed. And I have another table that holds all the requirements for in this case clients. That table looks like client_id, course_id. Lets call it Client_requirements.

So the client_requirements table can look like this:

client_id | course_id
1            3
1            4
1            7

The other table looks like this:

course_id| username| training_date
3          1234      date
4          1234      date
7          1234      date
7          4354      date

What I need to do is figure out based on the courses a user has taken is the user qualified. Qualified in this case just means they have taken all the courses the client requires. I feel like it's something obvious but my brain is melted at this point.

Im using oracle btw but I dont think that should matter for this kind of issue.

Upvotes: 0

Views: 84

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

If I understand correctly, you want join with a group by . . . and then having to be sure all the courses are accounted for:

select uc.user_id, cr.client_id
from user_courses uc join
     client_requirements cr
     on uc.course_id = cr.course_id
group by uc.user_id, cr.client_id
having count(*) = (select count(*) from client_requirements cr2 where cr2.client_id = cr.client_id);

This assumes that courses are not listed twice for the same user/client. That is easily taken into account, if that could happen.

The having clause checks that the number of matches for the user/client matches the total number of required courses for the client.

Upvotes: 2

Related Questions