query help

I need a query to return a list of courses which have not reached the enrollment capacity. So it will need to count the number of user/course pairs in student with a given courseid and select the fullname of courses that have not reached their capacity.

students.userid
students.courseid

course.id
course.fullname
course.capacity

query = SELECT fullname FROM course WHERE - The sum of students.userid with course.id < course.capacity

Can anybody help?

Hi jwoker,

i needed a query that looks like yours. I handled it with the following:


SELECT `courseid` AS cid, COUNT(*) AS cnt FROM `students` GROUP BY `courseid` HAVING cnt > (SELECT `capacity` FROM `course` WHERE `id` = cid)


I think that should do the trick.

Cheers,

Jaap

I tried your query but I got error 1164 check syntax near - 'Select `capacity...'

I tried your query but I got error 1164 check syntax near - 'Select `capacity...'

is your table called exactly 'course' and your column exactly 'capacity' ?

maybe you can try it without the ``. Furthermore, it is important you use the ` instead of the '.

and what is your MySQL version?