List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:December 16 2009 3:17pm
Subject:RE: Count records in join
View as plain text  
>-----Original Message-----
>From: Miguel Vaz [mailto:pagongski@stripped]
>Sent: Wednesday, December 16, 2009 9:39 AM
>To: Johan De Meersman
>Cc: Gavin Towey; mysql@stripped
>Subject: Re: Count records in join
>
>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
>
[JS] Add

HAVING COUNT(*) > 0

is one way.

I haven't been following the thread, but would

=====

SELECT `p`.`id_prod`, COUNT(`r`.`id_event`) `e` FROM
`programas` `p` LEFT JOIN `events` r
ON `p`.`id_prod` = `r`.`id_prod`
WHERE `r`.`id_prod` IS NOT NULL
GROUP BY `p`.`id_prod`;

=====

do what you want? That should find only those rows in `programmas` that match 
rows in `events`, and give you the number of events for each one.

Regards,

Jerry Schwartz
The Infoshop by Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341

www.the-infoshop.com



>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