I think putting the sub select in a join is also a lot more performant because it does not run the sub select for every row.
Kirk Cerny On Fri, Dec 25, 2009 at 10:36 AM, Randy Moller <[email protected]> wrote: > Wade; Try something like this using a join table, and avoid including > fields in your select that would produce a many -> 1 condition (such as > your user nickname field). btw, "join" in mysql defaults to "inner join" > in case you're wondering why i didn't specify. > > SELECT t.num_users, e.even_id, e.even_title, e.even_status, e.even_type, > UNIX_TIMESTAMP(e.even_start) AS `start`, > UNIX_TIMESTAMP(e.even_end) AS `end`, UNIX_TIMESTAMP(e.even_modified) AS > `modified` > FROM dev_cscca.events e > JOIN dev_cscca_cms.users u ON e.even_modifiedby=u.user_id > JOIN ( > SELECT COUNT(*) AS `num_users` FROM > dev_cscca_cms.users u2 WHERE u2.even_id = e.even_id > ) AS t > WHERE e.even_status IN(1,8) > ORDER BY e.even_title ASC > > Hope that helps. > > Randy > > Wade Preston Shearer wrote: >> 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 >> ------------------------------------------------------------------------ >> >> >> No virus found in this incoming message. >> Checked by AVG - www.avg.com >> Version: 8.5.430 / Virus Database: 270.14.119/2585 - Release Date: 12/24/09 >> 08:11:00 >> >> > > > _______________________________________________ > > UPHPU mailing list > [email protected] > http://uphpu.org/mailman/listinfo/uphpu > IRC: #uphpu on irc.freenode.net > _______________________________________________ UPHPU mailing list [email protected] http://uphpu.org/mailman/listinfo/uphpu IRC: #uphpu on irc.freenode.net
