[UPHPU] adding a count to an MySQL query

Wade Preston Shearer wadeshearer.lists at me.com
Thu Dec 24 11:12:07 MST 2009


I am stuck on an MySQL query and hoping that someone can help me out.  
I have a table that contains events. I have another many-to-many table  
that lists each event that a user is registered for. I want to  
retrieve all of the events with a count of how many users are  
registered for each.

This query…

SELECT even.even_id, even.even_title, even.even_status, even.even_type,
	UNIX_TIMESTAMP(even.even_start) as start,  
UNIX_TIMESTAMP(even.even_end) as end,
	UNIX_TIMESTAMP(even.even_modified) as modified, user.user_nickname
FROM dev_cscca.events even
	INNER JOIN dev_cscca_cms.users user
		ON even.even_modifiedby=user.user_id
WHERE even.even_status in(1,8)
ORDER BY even.even_title asc


…returns all of the events. I am not trying to enhance the query so  
that it includes the count of how many users are registered (for each  
event).

This…

SELECT even.even_id, even.even_title, even.even_status, even.even_type,
	UNIX_TIMESTAMP(even.even_start) as start,  
UNIX_TIMESTAMP(even.even_end) as end,
	UNIX_TIMESTAMP(even.even_modified) as modified, user.user_nickname, (
		SELECT COUNT(meev.meev_id)
		FROM dev_cscca.members_events meev
		WHERE meev.meev_date_scheduled is not NULL
		) as count
FROM dev_cscca.events even
	INNER JOIN dev_cscca_cms.users user
		ON even.even_modifiedby=user.user_id
WHERE even.even_status in(1,8)
ORDER BY even.even_title asc


…returns a count of how many users are registered, but it counts how  
many users are registered total, not how many for each of event. I  
have tried various joins, but have not been able to find the proper  
syntax for narrowing this down.


More information about the UPHPU mailing list