Re: Count records in join
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
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 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.bewrote: 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
RE: Count records in join
-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.bewrote: 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
Re: Count records in join
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.comwrote: -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
RE: Count records in join
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=arch...@jab.org
