Yes, that would do what you mentioned, show all programs with a count on events, but i need the opposite, show (and delete) all that dont have any events. Well, just have to use IS NULL instead. Thanks.
MV On Wed, Dec 16, 2009 at 3:17 PM, Jerry Schwartz <jschwa...@the-infoshop.com>wrote: > >-----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 > >>> > >>> > >> > > > >
