Find rows with the same values in a column-CodePudding

I've a table LESSON like this

| Student_id | Lesson_id |
| ---------- |- -------- |
| 352-03-3624|  10359427 |
| 352-03-3624|  10359449 |
| 805-17-4144|  58149917 |
| 805-17-4144|  58149968 |

I have to look for students who have taken the same lessons, ie who have the same lesson_id.

I used this query:

select * from lesson e
where exists
(select null from lesson i
 where e.lesson_id = i.lesson_if and e.student_id <> i.student_id)
 order by lesson_id

but it doesn't work very well.

Is there someone who can help me?

thanks

I'm finding for every studend if there is another student who follows the same lessons.

CodePudding user response：

Just use the HAVING clause:

WITH lessons AS
(
  SELECT '352-03-3624' as student_id, '10359427' as lesson_id FROM dual UNION ALL
  SELECT '352-03-3624', '10359449' FROM dual UNION ALL
  SELECT '805-17-4144', '58149917' FROM dual UNION ALL
  SELECT '805-17-4144', '58149968' FROM dual UNION ALL
  SELECT '805-17-4144', '10359427' FROM dual UNION ALL
  SELECT '805-17-4143', '10359427' FROM dual UNION ALL
  SELECT '805-17-4144', '10359449' FROM dual 
)
SELECT lsns.lesson_id, lsns.student_id
  FROM lessons lsns
     , (SELECT COUNT(1), lesson_id
          FROM lessons
         GROUP BY lesson_id
        HAVING COUNT(1) > 1) lsns_cnt
 WHERE lsns_cnt.lesson_id = lsns.lesson_id;

PS. I added more data in order to have a result set. It contains the course and the student that are matching the criteria.

CodePudding user response：

Another option might be to use count in its analytic form and then fetch rows whose count is larger than 1 (which means that there are two or more students who took the same lesson).

Sample data:

SQL> WITH lessons (student_id, lesson_id) AS
  2  (
  3    SELECT '352-03-3624', '10359427' FROM dual UNION ALL
  4    SELECT '352-03-3624', '10359449' FROM dual UNION ALL
  5    SELECT '805-17-4144', '58149917' FROM dual UNION ALL
  6    SELECT '805-17-4144', '58149968' FROM dual UNION ALL
  7    SELECT '805-17-4144', '10359427' FROM dual UNION ALL
  8    SELECT '805-17-4143', '10359427' FROM dual UNION ALL
  9    SELECT '805-17-4144', '10359449' FROM dual
 10  )

Query begins here:

 11  select lesson_id, student_id
 12  from (select lesson_id,
 13               student_id,
 14               count(*) over (partition by lesson_id) cnt
 15        from lessons
 16       )
 17  where cnt > 1
 18  order by lesson_id, student_id;

LESSON_I STUDENT_ID
-------- -----------
10359427 352-03-3624
10359427 805-17-4143
10359427 805-17-4144
10359449 352-03-3624
10359449 805-17-4144

SQL>