List:General Discussion« Previous MessageNext Message »
From:Miguel Vaz Date:December 16 2009 2:38pm
Subject:Re: Count records in join
View as plain text  
Thanks all for the feedback. Here's what i did:

select p.id_prog,count(r.id_event) e from programas p left join(events r)
on(p.id_prog=r.id_prog) group by r.id_event

This gives me a list of all the distinct progs with a count of how many
events on each. I then delete the empty ones.

It would be nice to be able to delete the empty ones on the same query.


MV



On Wed, Dec 16, 2009 at 1:48 PM, Johan De Meersman <vegivamp@stripped>wrote:

> If the aim is purely to find the progs without events, it might be more
> efficient to use something like
>
> select * from progs where not exist (select id_prog from events where
> id_prog = progs.id_prog);
>
> My syntax might be off, check "not exists" documentation for more info.
>
>
> On Tue, Dec 15, 2009 at 8:14 PM, Gavin Towey <gtowey@stripped> wrote:
>
>> Hi Miguel,
>>
>> You'll need to use LEFT JOIN, that will show all records that match and a
>> row in the second table will all values NULL where there is no match.  Then
>> you find all those rows that have no match in your WHERE clause.
>>
>> Regards,
>> Gavin Towey
>>
>> -----Original Message-----
>> From: Miguel Vaz [mailto:pagongski@stripped]
>> Sent: Tuesday, December 15, 2009 10:43 AM
>> To: mysql@stripped
>> Subject: Count records in join
>>
>> Hi,
>>
>> I am stuck with a suposedly simple query:
>>
>> - i have two tables (:
>>
>> PROGS
>> id_prog
>> name
>>
>> EVENTS
>> id
>> id_prog
>> name
>>
>> How can i list all records from PROGS with a sum of how many events each
>> have? I want to find the progs that are empty.
>>
>> I remember something about using NULL, but i cant remember. :-P
>>
>> Thanks.
>>
>> MV
>>
>> This message contains confidential information and is intended only for
>> the individual named.  If you are not the named addressee, you are notified
>> that reviewing, disseminating, disclosing, copying or distributing this
>> e-mail is strictly prohibited.  Please notify the sender immediately by
>> e-mail if you have received this e-mail by mistake and delete this e-mail
>> from your system. E-mail transmission cannot be guaranteed to be secure or
>> error-free as information could be intercepted, corrupted, lost, destroyed,
>> arrive late or incomplete, or contain viruses. The sender therefore does not
>> accept liability for any loss or damage caused by viruses or errors or
>> omissions in the contents of this message, which arise as a result of e-mail
>> transmission. [FriendFinder Networks, Inc., 220 Humbolt court, Sunnyvale, CA
>> 94089, USA, FriendFinder.com
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>
>>
>

Thread
Count records in joinMiguel Vaz15 Dec
  • RE: Count records in joinGavin Towey15 Dec
    • Re: Count records in joinJohan De Meersman16 Dec
      • Re: Count records in joinMiguel Vaz16 Dec
        • RE: Count records in joinJerry Schwartz16 Dec
          • Re: Count records in joinMiguel Vaz16 Dec