CODEWITHSUNDEEP
sqlpostgresql
Andrew Stevenson
Andrew Stevenson

Reputation: 658

How do I display matching rows for multiple users in Postgres?

I'm new to Postgres and I'm looking to retrieve a list of courses that a particular set of users are enrolled in as course leaders in the same course(s). Hopefully I've explained that clearly.

It's returning a list of all the courses that that all the users are enrolled in as course leader rather than a list of the same courses that they are enrolled in as course leader.

This is my original query

select distinct(cm.course_id), cm.course_name, cm.dtcreated, cm.dtmodified 
from course_main cm
inner join course_users cu on cm.pk1 = cu.crsmain_pk1
inner join users u on u.pk1 = cu.users_pk1
where u.user_id IN ('msassar5','mfztsjc3', 'mzysshba', 'mftssag3', 'mfztslmi', 'mfztsml7', 'mtlsscm5', 'msdsshp2', 'mzysscp9', 'mcyssmy')
and role = 'P'  - 'P' denotes course leader
and cm.service_level = 'F' -- 'F' denotes full course
order by cm.course_id, cm.dtcreated asc

I need to compare all the courses that each of the users are enrolled in as course leader (P) and only display the courses that are in each list, but I'm not sure the best approach.

I've read about the INTERSECT statement and using that does produce the result I expect, but that seems to be an inelegant solution as I would have to add another query to the INTERSECT list for each additional user.

Here is me INTERSECT query

select distinct(cm.course_id), cm.course_name, cm.dtcreated, cm.dtmodified 
from course_main cm
inner join course_users cu on cm.pk1 = cu.crsmain_pk1
left outer join users u on u.pk1 = cu.users_pk1
where u.user_id = '<USERNAME>'
and role = 'P'
and cm.service_level = 'F'
    intersect
select distinct(cm.course_id), cm.course_name, cm.dtcreated, cm.dtmodified 
from course_main cm
inner join course_users cu on cm.pk1 = cu.crsmain_pk1
left outer join users u on u.pk1 = cu.users_pk1
where u.user_id = '<USERNAME>'
and role = 'P'
and cm.service_level = 'F'
    intersect
    ...
    ...
    ...

Here is my result, which is correct. This is the only course that ALL the users in my query are enrolled in

Is there a more elegant solution?

Any help will be appreciated.

thanks

Upvotes: 1

Views: 112

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270011

If you want an intersection -- that is courses where all 10 users are leaders -- then use aggregation and a having clause:

select cm.course_id, cm.course_name, cm.dtcreated, cm.dtmodified 
from course_main cm join
     course_users cu
     on cm.pk1 = cu.crsmain_pk1 join
     users u
     on u.pk1 = cu.users_pk1
where u.user_id in ('msassar5', 'mfztsjc3', 'mzysshba', 'mftssag3', 'mfztslmi', 'mfztsml7', 'mtlsscm5', 'msdsshp2', 'mzysscp9', 'mcyssmy') and
      role = 'P' and
      cm.service_level = 'F' -- 'F' denotes full course
group by cm.course_id
having count(*) = 10;

Upvotes: 1

Related Questions