SQL count(), trying to count rows from two tables and restrict info displayed
I am setting up a database that has a limit to the amount of people that can register for a course. In my course table I have a Maximum column which holds an int and in my registered table it holds all the registered people for that course. SO my web page should only display course times where the number of registered people in the registered table < maximum allowed in the course table. Heres my current query.
SELECT course.time,COUNT(registered.time),registered.maximum FROM course JOIN (registered) ON course.type = 'computing' OR (registered.type = 'computing' AND course.time = registered.time) GROUP BY course.time HAVING COUNT(registered.time) < course.maximum
The problem with this is it counts all the registered people regardless of the course time, so if there is 2 people registered for different times it still counts 2 people registered for each time.
Hope I explained it ok and hope someone can help. Thanks
It is unclear to me why you have registered.maximum and course.maximum. And it would be easier to answer the question if we knew the actual table structure, rather than having to guess based on your code. But here goes:
SELECT course.time, COUNT(registered.time), registered.maximum FROM course JOIN registered ON course.type = registered.type AND course.time = registered.time WHERE course.type = 'computing' GROUP BY course.time, registered.maximum HAVING COUNT(registered.time) < course.maximum
I also did a group by on registered.maximum, because you will get a syntax error otherwise.
Finally, I changed the join to be on the type, and then added it to the WHERE clause. To me, that always makes more sense, though it may sometimes be slower. From my perspective, the JOIN clause is just for joining, while the WHERE clause is for filtering.
UPDATE: to also get courses with no one registered:
SELECT course.time, COUNT(registered.time), course.maximum FROM course LEFT OUTER JOIN registered ON course.type = registered.type AND course.time = registered.time WHERE course.type = 'computing' GROUP BY course.time, course.maximum HAVING COUNT(registered.time) < course.maximum