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

Answers


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 

Need Your Help

If you started iOS programming today, what frameworks would you consider still necessary and useful?

xcode rest ios6 xcode4.5 libraries

I've recently started iOS development, coming into it as a web developer. I couldn't imagine not using tools like jquery and modernizor. (I know plenty of other people could, but that's them)

Inter-module communication in NetBeans RCP application

java rcp netbeans-platform

I'm well aware of how an object instantiated in a module can be shared with other different modules by means of instance content and lookup.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.