...

View Full Version : query help



jwoker
08-18-2005, 09:03 PM
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?

jaap
08-19-2005, 10:11 AM
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

jwoker
08-19-2005, 05:37 PM
I tried your query but I got error 1164 check syntax near - 'Select `capacity...'

jaap
08-19-2005, 05:45 PM
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?



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum