List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:December 16 2009 1:48pm
Subject:Re: Count records in join
View as plain text  
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