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.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 >> >> >
