hi,
I want to display the name and email of the students who are registered for ALL modules.
this is my SQL expression and error I Am getting. any assistance would be appreciated.
mysql> SELECT S.SName,S.EMail
-> FROM registration R,student S
-> WHERE R.StudentId=S.StudentId
-> GROUP BY S.StudentId,S.SName,S.EMail
-> HAVING COUNT(*)=(SELECT COUNT
-> FROM module);
Error: ERROR 1054 (42S22): Unknown column 'COUNT' in 'field list'
the tables are below:
module;
+----------+------------+-------+--------+---------+----------+
| ModuleId | MName | MType | MLevel | Credits | Syllabus |
+----------+------------+-------+--------+---------+----------+
| M1 | Database | 10 | 1 | 1 | Old |
| M2 | Web | 15 | 2 | 3 | New |
| M3 | DIS | 12 | 1 | 6 | New |
| M4 | ECommerce | 20 | 3 | 9 | Old |
| M5 | Management | 20 | 2 | 3 | New |
| M6 | Business | 10 | 1 | 6 | Old |
+----------+------------+-------+--------+---------+----------+
registration;
+-----------+----------+--------+
| StudentId | ModuleId | Result |
+-----------+----------+--------+
| S1 | M5 | NULL |
| S2 | M5 | NULL |
| S3 | M1 | 76 |
| S3 | M2 | NULL |
| S3 | M5 | NULL |
| S5 | M1 | 45 |
| S5 | M2 | 32 |
| S5 | M3 | 25 |
| S5 | M4 | 90 |
| S5 | M5 | 85 |
| S5 | M6 | 45 |
+-----------+----------+--------+
student;
+-----------+---------+---------+-----+---------------------+
| StudentId | SName | Address | Age | EMail |
+-----------+---------+---------+-----+---------------------+
| S1 | John | Curepe | 20 |
John@sbcs.com |
| S10 | Brown | Arima | 24 |
Brown@hotmail.com |
| S2 | Patrick | Couva | 24 |
Patrick@sbcs.com |
| S3 | Mary | Curepe | 29 |
Mary@sbcs.com |
| S4 | Hank | Curepe | 44 |
Hank@hotmail.com |
| S5 | Harry | Maracas | 34 |
Harry@sbcs.com |
| S6 | Smith | Arima | 24 |
Smith@gmail.com |
| S7 | White | Curepe | 24 |
White@sbcs.com |
| S8 | Charles | Penal | 25 |
Charles@hotmail.com |
| S9 | Khan | Arima | 25 |
Khan@gmail.com |
+-----------+---------+---------+-----+---------------------+