SQL: Compare two rows of same attribute grouped by another attribute

Question

I want to compare one attribute of different rows but only when those rows have another attribute in common. example Table Students

+------+---------+---------+-------+------+
| snum | sname   | major   | level | age  |
+------+---------+---------+-------+------+
|  101 | JOHN    | CS      | SR    |   19 |
|  102 | SMITH   | CS      | JR    |   20 |
|  103 | JACOB   | ECE     | SR    |   20 |
|  104 | TOM     | CS      | JR    |   20 |
|  105 | SID     | CS      | JR    |   20 |
|  106 | HARRY   | HISTORY | SR    |   21 |
|  107 | HELLEN  | CS      | JR    |   21 |
|  108 | BOB     | ENGLISH | SR    |   22 |
|  109 | ANDY    | ECE     | JR    |   21 |
|  110 | CHARLES | HISTORY | SR    |   23 |
+------+---------+---------+-------+------+

Table Class where cname is for Course name and fid is for faculty id and you can now ignore that because I haven't included that table as there is no need of that here.

+--------+----------+------+------+
| cname  | meets_at | room | fid  |
+--------+----------+------+------+
| CSC342 | Morning  | R128 |  201 |
| CSC343 | Noon     | R128 |  203 |
| CSC345 | Night    | R154 |  204 |
| ECE300 | Morning  | R111 |  202 |
| ECE301 | Noon     | R111 |  203 |
| ENG366 | Morning  | R154 |  203 |
| ENG367 | Evening  | R111 |  205 |
| HIS320 | Evening  | R128 |  205 |
+--------+----------+------+------+

Table Enrolled - Connecting link between above two tables and tell which student is in which course.

+------+--------+
| snum | cname  |
+------+--------+
|  101 | CSC342 |
|  101 | CSC343 |
|  101 | CSC345 |
|  101 | ECE300 |
|  101 | ENG366 |
|  102 | CSC343 |
|  102 | CSC345 |
|  102 | ECE301 |
|  103 | ECE300 |
|  103 | ECE301 |
|  104 | CSC342 |
|  104 | ECE301 |
|  105 | CSC345 |
|  105 | ECE300 |
|  106 | ENG366 |
|  106 | HIS320 |
|  107 | CSC342 |
|  107 | ENG366 |
|  108 | ENG367 |
|  108 | HIS320 |
|  109 | ECE300 |
|  109 | ECE301 |
|  110 | ENG366 |
|  110 | HIS320 |
+------+--------+

I want to find names of all those students who have clash in there courses timing (meets_at).

I have tried following(this is surely not close to solution because i am not sure of what should be tried).

SELECT * FROM Student 
NATURAL JOIN Class C1 
NATURAL JOIN Class C2 
NATURAL JOIN Enrolled 
WHERE C1.meets_at = C2.meets_at 
GROUP BY snum;

I have selected * because if i will be satisfied with the response i will change it to sname. I wanted to somehow compare the meets_at attribute of same student of different cname (course name) that's why i am trying to group them. Response of the above query(obviously you can already expect but just for saving your mental energy.)

+--------+------+----------+------+------+---------+---------+-------+------+
| cname  | snum | meets_at | room | fid  | sname   | major   | level | age  |
+--------+------+----------+------+------+---------+---------+-------+------+
| CSC342 |  101 | Morning  | R128 |  201 | JOHN    | CS      | SR    |   19 |
| CSC343 |  102 | Noon     | R128 |  203 | Smith   | CS      | JR    |   20 |
| ECE300 |  103 | Morning  | R111 |  202 | JACOB   | ECE     | SR    |   20 |
| CSC342 |  104 | Morning  | R128 |  201 | TOM     | CS      | JR    |   20 |
| CSC345 |  105 | Night    | R154 |  204 | SID     | CS      | JR    |   20 |
| ENG366 |  106 | Morning  | R154 |  203 | HARRY   | HISTORY | SR    |   21 |
| CSC342 |  107 | Morning  | R128 |  201 | HELLEN  | CS      | JR    |   21 |
| ENG367 |  108 | Evening  | R111 |  205 | BOB     | ENGLISH | SR    |   22 |
| ECE300 |  109 | Morning  | R111 |  202 | ANDY    | ECE     | JR    |   21 |
| ENG366 |  110 | Morning  | R154 |  203 | CHARLES | HISTORY | SR    |   23 |
+--------+------+----------+------+------+---------+---------+-------+------+

A little more explanation with example on what i want(not necessary if you already understood). I want to get the name of JOHN, SMITH and HELEN because they have more than one cname having same meets_at attribute in the following table(made by joining all the tables).

+--------+------+----------+------+------+---------+---------+-------+------+
| cname  | snum | meets_at | room | fid  | sname   | major   | level | age  |
+--------+------+----------+------+------+---------+---------+-------+------+
| CSC342 |  101 | Morning  | R128 |  201 | JOHN    | CS      | SR    |   19 |
| CSC343 |  101 | Noon     | R128 |  203 | JOHN    | CS      | SR    |   19 |
| CSC345 |  101 | Night    | R154 |  204 | JOHN    | CS      | SR    |   19 |
| ECE300 |  101 | Morning  | R111 |  202 | JOHN    | CS      | SR    |   19 |
| ENG366 |  101 | Morning  | R154 |  203 | JOHN    | CS      | SR    |   19 |
| CSC343 |  102 | Noon     | R128 |  203 | Smith   | CS      | JR    |   20 |
| CSC345 |  102 | Night    | R154 |  204 | Smith   | CS      | JR    |   20 |
| ECE301 |  102 | Noon     | R111 |  203 | Smith   | CS      | JR    |   20 |
| ECE300 |  103 | Morning  | R111 |  202 | JACOB   | ECE     | SR    |   20 |
| ECE301 |  103 | Noon     | R111 |  203 | JACOB   | ECE     | SR    |   20 |
| CSC342 |  104 | Morning  | R128 |  201 | TOM     | CS      | JR    |   20 |
| ECE301 |  104 | Noon     | R111 |  203 | TOM     | CS      | JR    |   20 |
| CSC345 |  105 | Night    | R154 |  204 | SID     | CS      | JR    |   20 |
| ECE300 |  105 | Morning  | R111 |  202 | SID     | CS      | JR    |   20 |
| ENG366 |  106 | Morning  | R154 |  203 | HARRY   | HISTORY | SR    |   21 |
| HIS320 |  106 | Evening  | R128 |  205 | HARRY   | HISTORY | SR    |   21 |
| CSC342 |  107 | Morning  | R128 |  201 | HELLEN  | CS      | JR    |   21 |
| ENG366 |  107 | Morning  | R154 |  203 | HELLEN  | CS      | JR    |   21 |
| ENG367 |  108 | Evening  | R111 |  205 | BOB     | ENGLISH | SR    |   22 |
| HIS320 |  108 | Evening  | R128 |  205 | BOB     | ENGLISH | SR    |   22 |
| ECE300 |  109 | Morning  | R111 |  202 | ANDY    | ECE     | JR    |   21 |
| ECE301 |  109 | Noon     | R111 |  203 | ANDY    | ECE     | JR    |   21 |
| ENG366 |  110 | Morning  | R154 |  203 | CHARLES | HISTORY | SR    |   23 |
| HIS320 |  110 | Evening  | R128 |  205 | CHARLES | HISTORY | SR    |   23 |
+--------+------+----------+------+------+---------+---------+-------+------+
24 rows in set (0.001 sec)

Answer 1

What you want to do is group by the student and the 'meets at' property, and then simply count how many records are associated with each combination.

If there is more than one record associated with JOHN, Morning then this indicates a clash, so you are looking for the case where the count is greater than 1:

      SELECT snum, sname, meets_at, COUNT(*) > 1 AS has_clash
        FROM Student
NATURAL JOIN Enrolled
NATURAL JOIN Class
    GROUP BY snum, sname, meets_at

Note how I am grouping by snum (unique to a student) but also have to group by sname, since this is what you want to report (the name may not be unique to a student, so grouping by snum is still necessary).

This will return a table of student IDs & names, the times they meet for classes, and a boolean column indicating whether they have a clash at that time or not.

You could filter this with a HAVING COUNT(*) > 1 clause after the GROUP BY if you only want to see those students who have clashes.