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

ShibbirAhmed
September 1, 2022
171 views
0 votes
2 Answers

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.

Answers

- alexey
- September 1, 2022 at 6:08 pm
- 0 votes
0
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
Login or Signup to reply.

- PepeNO
- September 1, 2022 at 7:37 pm
- 0 votes
0
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
Login or Signup to reply.