Get id with same value-CodePudding

I have a table as below:

studentNo  courseNo 
s001         c001  
s001         c002
s002         c001
s002         c002
s003         c001
s003         c003

May I know how can I get the studentNo who have exactly same course as student s001? Many thanks.

CodePudding user response：

You want to count the number of courses for each student and then join the table to itself for different students taking the same course with the same total number of courses and then count that the total number of matched rows in the join equals total number of courses:

WITH course_count (studentno, courseno, num_courses) AS (
  SELECT t.*,
         COUNT(courseNo) OVER (PARTITION BY studentno)
  FROM   table_name t
)
SELECT c.studentno
FROM   course_count c
       INNER JOIN course_count s
       ON (   c.courseno   =  s.courseno
          AND c.studentno  != s.studentno
          AND c.num_courses =  s.num_courses )
WHERE  s.studentno = 's001'
GROUP BY c.studentno
HAVING COUNT(c.courseno) = MAX(s.num_courses);

Which, for the (expanded) sample data:

CREATE TABLE table_name (studentNo, courseNo) AS
SELECT 's001', 'c001' FROM DUAL UNION ALL 
SELECT 's001', 'c002' FROM DUAL UNION ALL
SELECT 's002', 'c001' FROM DUAL UNION ALL
SELECT 's002', 'c002' FROM DUAL UNION ALL
SELECT 's003', 'c001' FROM DUAL UNION ALL
SELECT 's003', 'c003' FROM DUAL UNION ALL
SELECT 's004', 'c002' FROM DUAL UNION ALL
SELECT 's004', 'c001' FROM DUAL UNION ALL
SELECT 's004', 'c003' FROM DUAL UNION ALL
SELECT 's005', 'c001' FROM DUAL

Outputs:

STUDENTNO

s002

STUDENTNO
s002

db<>fiddle here

CodePudding user response：

You didn't specify database you use; though, most people - who use Oracle SQL Developer as a tool - use Oracle database. Presuming that this really is so, here's one option.

Sample data:

SQL> select * from study order by studentno, courseno;

STUDENTNO  COURSENO
---------- ----------
s001       c001
s001       c002
s002       c001
s002       c002
s003       c001
s003       c003
s004       c001
s004       c002
s004       c003

9 rows selected.

The temp CTE aggregates all courses for each student. listagg's order by clause makes sure that courses will be properly sorted.

Then, in the final select statement (which begins at line #7), query returns students (who aren't s001 (line #9) as you're comparing the rest of them to s001) whose list of courses is the same as list of courses for student s001 (that's what subquery in lines #10 - 12 returns).

SQL> with temp as
  2    (select studentno,
  3       listagg(courseno, ';') within group (order by courseno) courses
  4     from study
  5     group by studentno
  6    )
  7  select studentno
  8  from temp
  9  where studentno <> 's001'
 10    and courses = (select courses
 11                   from temp
 12                   where studentno = 's001');

STUDENTNO
----------
s002

SQL>

Similarly, using self-join:

SQL> with temp as
  2    (select studentno,
  3       listagg(courseno, ';') within group (order by courseno) courses
  4     from study
  5     group by studentno
  6    )
  7  select b.studentno
  8  from temp a join temp b on a.studentno <> b.studentno
  9                         and a.courses = b.courses
 10  where a.studentno = 's001';

STUDENTNO
----------
s002

SQL>

CodePudding user response：

EDIT: My solution is flawed. I will fix it later when I get back from work. for now, check the solution MTO posted. since it does return the expected results Good day and have a great week ahead. it's still Monday morning so I might be wrong. but this should work as you need

what you need here is a subQuery, subquery will return all the courses of S001. then the outer query will use the results to return all the students who have the same courses. Note that the results will appear once for each student

SELECT DISTINCT studentNo
FROM tableName
WHERE CourseNo IN (
        SELECT CourseNo
        FROM TableName
        WHERE studentNo = 's001'
        )

Fiddle : https://www.db-fiddle.com/f/8k2Ye6deGSjLj9o9THYyys/0

Fiddle No 2: https://www.db-fiddle.com/f/8k2Ye6deGSjLj9o9THYyys/1 The Second Fiddle is not the best in design but it should do what you asked

Code for Fiddle 2

SELECT DISTINCT studentNo
FROM test
WHERE studentNo NOT IN (
        SELECT studentNo
        FROM test
        WHERE CourseNo NOT IN (
                SELECT CourseNo
                FROM test
                WHERE studentNo = 's001'
                )
            AND studentNo != 's001'
        )