[UPHPU] adding a count to an MySQL query

Wade Preston Shearer wadeshearer.lists at me.com
Fri Dec 25 21:17:37 MST 2009


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 <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