I have a table like this:

I am looking for a query that returns only these values:

I expect only those couple of students_1 and student_2 with all lesson_id of student_1. I am looking for those pairs (student_1, student_2) in which all the lesson_id of student_1 are present. In the example above, the pair (352-03-3624 and 805-17-4144) is ok because it's present with lesson_id 27,49 and 50, but the pair 352-03-3624 and 805-17-4143 isn't ok beacuse there is only lesson_id 27 while the lesson_id 49 e 50 are missing.

I hope I was clear.

CodePudding user response：

You have not explained the logic you want to use, if you just want those rows then whitelist them in the query:

SELECT *
FROM   table_name
WHERE  (Student_1, Student_2, lesson_id) IN (
         ('352-03-3624', '805-17-4144', 27),
         ('352-03-3624', '805-17-4144', 49),
         ('352-03-3624', '805-17-4144', 50),
         ('805-17-4143', '805-17-4144', 27),
         ('805-17-4143', '805-17-4144', 68)
       );

But you could also find rows where there are multiple student_1 and student_2 pairs:

SELECT student_1, student_2, lesson_id
FROM   (
  SELECT t.*,
         COUNT(*) OVER (PARTITION BY student_1, student_2) AS cnt
  FROM   input_table t
  WHERE  student_1 < student_2
)
WHERE  cnt > 1

Or even where there are multiple duplicate student pairs which also have the student numbers reversed:

SELECT student_1, student_2, lesson_id
FROM   (
  SELECT t.*,
         COUNT(*) OVER (PARTITION BY student_1, student_2) AS cnt
  FROM   input_table t
  WHERE  (student_1, student_2, lesson_id)
           IN ( SELECT student_2, student_1, lesson_id FROM input_table )
  AND    student_1 < student_2
)
WHERE  cnt > 1

All of the queries output your desired output.

fiddle

CodePudding user response：

Simply count the lessons and compare the lesson counts:

select student_1, student_2, lesson_id
from
(
  select 
    student_1, student_2, lesson_id,
    count (distinct lesson_id) over (partition by student_1) as s1_lesson_count,
    count (*) over (partition by student_1, student_2) as s1_s2_lesson_count
  from mytable
)
where s1_lesson_count = s1_s2_lesson_count
order by student_1, student_2, lesson_id;