Hello,

I have a table student_class which tells which student has visited
which class.
Now I would like to check if each student has visited each 'required'
class exactly one time.

e.g. the required classes are Class_ID = 1, Class_ID = 2 and Class_ID
= 3.

now for each known student should be checked, if he has per required
class exactly one entry in the student_class table.
what is the best way to do it?
Is there a way which avoids foreach-loops?

Regards
Ralph

szeta <szetaa@gmail.com> wrote in
<1186394129.789372.15070@l70g2000hse.googlegroups. com>:
> I have a table student_class which tells which student has
> visited which class.
> Now I would like to check if each student has visited each
> 'required' class exactly one time.
>
> e.g. the required classes are Class_ID = 1, Class_ID = 2
> and Class_ID = 3.
>
> now for each known student should be checked, if he has
> per required class exactly one entry in the student_class
> table. what is the best way to do it?
> Is there a way which avoids foreach-loops?

Assuming your required classes are tucked away somewhere in
a table, a really ugly solution like the following is
possible:

mysql> SELECT * FROM student_class;
+------------+----------+
| student_id | class_id |
+------------+----------+
| 1 | 1 |
| 2 | 1 |
| 2 | 2 |
| 3 | 2 |
| 3 | 3 |
| 4 | 3 |
| 4 | 1 |
| 4 | 2 |
| 5 | 1 |
| 5 | 3 |
| 5 | 2 |
| 5 | 2 |
| 6 | 1 |
| 6 | 2 |
| 6 | 3 |
| 7 | 1 |
| 7 | 2 |
| 7 | 1 |
+------------+----------+
18 rows in set (0.00 sec)

mysql> SELECT * FROM required_class;
+----------+
| class_id |
+----------+
| 1 |
| 2 |
| 3 |
+----------+
3 rows in set (0.01 sec)

mysql> SELECT st.student_id,GROUP_CONCAT(st_cl.class_id
ORDER BY st_cl.class_id) AS req_cl FROM (SELECT DISTINCT
student_id FROM student_class) AS st JOIN student_class AS
st_cl ON (st.student_id=st_cl.student_id) GROUP BY
st.student_id HAVING req_cl<>(SELECT GROUP_CONCAT(class_id
ORDER BY class_id) FROM required_class);
+------------+---------+
| student_id | req_cl |
+------------+---------+
| 1 | 1 |
| 2 | 1,2 |
| 3 | 2,3 |
| 5 | 1,2,2,3 |
| 7 | 1,1,2 |
+------------+---------+
5 rows in set (0.00 sec)

mysql>

Perhaps someone else can come up with something better,
because, personally, I would try to avoid something like
this. GROUP_CONCAT() is quite... yeeeuchh.

--
....the pleasure of obedience is pretty thin compared with
the pleasure of hearing a rotten tomato hit someone in the
rear end. -- Garrison Keillor

Hey Garrison,

thanks for your reply!
group_concat is exactly what I have been looking for. :-)

regards
Ralph