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.
_______________________________________________
UPHPU mailing list
[email protected]
http://uphpu.org/mailman/listinfo/uphpu
IRC: #uphpu on irc.freenode.net
