Finding duplicate student entries in courses within the same topic

Data and Databases
1

Hey everyone! I’m working on a database for a school system and I’m stuck. Here’s what I’ve got:

CREATE TABLE topic (
  id INT PRIMARY KEY,
  title VARCHAR(50)
);

CREATE TABLE lesson (
  id INT PRIMARY KEY,
  title VARCHAR(50),
  topic_id INT,
  FOREIGN KEY (topic_id) REFERENCES topic(id)
);

CREATE TABLE student (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  lesson_id INT,
  FOREIGN KEY (lesson_id) REFERENCES lesson(id)
);

I’ve added some data to these tables. Now I need to find students who are enrolled in multiple lessons under the same topic. I tried this query:

SELECT s.name, t.title
FROM student s
JOIN lesson l ON s.lesson_id = l.id
JOIN topic t ON l.topic_id = t.id;

But it’s showing all students. How can I modify this to only show students who appear more than once in lessons with the same topic? Any help would be awesome!

2

hey, have u tried a subquery approach? for example:

SELECT s.name, t.title
FROM student s
JOIN lesson l ON s.lesson_id = l.id
JOIN topic t ON l.topic_id = t.id
WHERE s.name IN (
  SELECT s2.name
  FROM student s2
  JOIN lesson l2 ON s2.lesson_id = l2.id
  GROUP BY s2.name, l2.topic_id
  HAVING COUNT(*) > 1
);

thoughts?

3

I’ve encountered a similar issue in my work with educational databases. To solve this, you might want to consider using a self-join approach. Here’s a query that could work:

SELECT DISTINCT s1.name, t.title
FROM student s1
JOIN lesson l1 ON s1.lesson_id = l1.id
JOIN topic t ON l1.topic_id = t.id
JOIN student s2 ON s1.name = s2.name AND s1.id <> s2.id
JOIN lesson l2 ON s2.lesson_id = l2.id
WHERE l1.topic_id = l2.topic_id;

This query compares each student entry with other entries of the same student, checking if they’re enrolled in different lessons within the same topic. It should effectively identify the duplicate enrollments you’re looking for.

4

hey nova73, try grouping by student and topic and add a having clouse. for example, add at the end:

GROUP BY s.name, t.title
HAVING COUNT(*) > 1;

should filter out duplicates. hope this helps!