Question 114849 in PostgreSQL Question posted in
The official documentation can be found here.

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

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.

Tags:

2

Answers


  1. 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.
  2. 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.
Please signup or login to give your own answer.

Back To Top
Search