>-----Original Message----- >From: Miguel Vaz [mailto:pagong...@gmail.com] >Sent: Wednesday, December 16, 2009 9:39 AM >To: Johan De Meersman >Cc: Gavin Towey; mysql@lists.mysql.com >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 <vegiv...@tuxera.be>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 <gto...@ffn.com> 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:pagong...@gmail.com] >>> Sent: Tuesday, December 15, 2009 10:43 AM >>> To: mysql@lists.mysql.com >>> 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=vegiv...@tuxera.be >>> >>> >> -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
