Groups | Search | Server Info | Login | Register

Groups > comp.databases.mysql > #527

Re: Count question

Show all headers | View raw

On or about 4/7/2011 2:54 PM, it came to pass that Some One wrote:
> Using MyISam. Assuming a simple table structure of Events with another
> table of Event_Bands with 0-many bands associated with each row in Events.
>
> I want to know the total count of bands associated with the event when a
> specific band is associated with an event. I also need the data from the
> row for the band in the where clause and want the result in a single row.
>
> I got this working but I am wondering if there is an easier/faster way
> to do this
>
> select *,event_id as A, (select count(*) from event_bands group by
> event_id where event_id = A) from event_bands where band_id=1
>
> for example
> Event 1 count should be 2
>
> band id 1
> band id 2
>
> Event 2 count should be 1
>
> band id 1
>
> Event 3 (not selected)
>
> band id 4
>
> Thank you
> Arn

Clarification
SQL should read
select *,event_id as A,(select count(*) from event_items where event_id 
= A) from event_items where band_id=1

Back to comp.databases.mysql | Previous | Next — Previous in thread | Find similar

Thread

Count question Some One <someone@notyahoo.net> - 2011-04-07 14:54 -0400
  Re: Count question Some One <someone@notyahoo.net> - 2011-04-07 15:08 -0400

csiph-web