Match a Specific combination of values from a one to many relationship mapping table in postgres-CodePudding

I have a one to many relationship mapping table in postgres and the tables are as below:

Students

id	Name
1	Shibbir
2	Ahmed
3	John

Courses

id	Name
1	Data Structure
2	Algorithms
3	Math

student_course_mapping

studentId	courseId
1	1
1	2
1	3
2	2
2	3
3	1
3	2

Given that a student must have unique course combinations: if student 1 have the course 1,2,3 then none of the other students can have same course combinations, thus student 2 has to to take course combination 2,3 and then finally student 3 has to take course combination 1,2.

Finally my question is: if I have a combination of course ids, then how to write the query to get the unique student id from student_course_mapping table who has exactly the same combination of courses.

example : input course ids: 1,2,3 then output should be 1 input course ids: 2,3 then output should be 2 input course ids: 1,2 then output should be 3

It would be great to be able to write a single query that gives the desired result.

CodePudding user response：

To get the values position insensitive solution you can use arrays functions and operators - the ARRAY_AGG function to create arrays of courseid values for each studentid, the @> operator to check if courseid array of a student contains the array of the input courseid, the ARRAY_LENGTH to check if arrays have equal length.

SELECT 
    input.value,
    s.studentid
FROM (VALUES (ARRAY[2, 1, 3]), (ARRAY[2, 3]), (ARRAY[1, 2])) input(value)
JOIN (
    SELECT 
        studentid,
        ARRAY_AGG(courseid) course_ids
    FROM student_course_mapping 
    GROUP BY studentid
) s ON s.course_ids @> input.value AND ARRAY_LENGTH(s.course_ids, 1) = ARRAY_LENGTH(input.value, 1)

Output

value	studentid
{1,2}	3
{2,3}	2
{2,1,3}	1

db<>fiddle here

CodePudding user response：

In this case is necessary to create a temporary table because it's not possible to include aggregations in where clause

create temp table temp_students_courses
as select s.id, s.name, array_agg(scm.courseid order by courseid) courses 
from students s
inner join student_course_mapping scm on s.id=scm.studentid
group by 1,2;

then

select t1.*  
from temp_students_courses t1
where exists ( select * 
    from temp_students_courses t2
    where t1.id<>t2.id
    and t1.courses@>t2.courses
    and array_length(t1.courses,1)=array_length(t2.courses,1)
);

Since in your sample data doesn't occur the case that you expect, I added an extra row in student_course_mapping to exemplify when two students have same combination of courses.

To test https://www.db-fiddle.com/f/tEWNzwHX2YhtuRhoohyh4B/0