[UPHPU] adding a count to an MySQL query

Kirk Cerny kirksemail at gmail.com
Fri Dec 25 12:32:33 MST 2009


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 <zoomerz at comcast.net> 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
>> UPHPU at uphpu.org
>> 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
> UPHPU at uphpu.org
> http://uphpu.org/mailman/listinfo/uphpu
> IRC: #uphpu on irc.freenode.net
>


More information about the UPHPU mailing list