Thanks, Randy and Krik. I tried moving the subqueries into joins, but am getting an error. Here is my working query and the subquery-in-join attempt that is throwing an error:
http://stikked.com/view/raw/70776791 On 25 Dec 2009, at 12:32, Kirk Cerny wrote: > 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
