List:General Discussion« Previous MessageNext Message »
From:Miguel Vaz Date:December 16 2009 3:27pm
Subject:Re: Count records in join
View as plain text  
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
<jschwartz@stripped>wrote:

> >-----Original Message-----
> >From: Miguel Vaz [mailto:pagongski@stripped]
> >Sent: Wednesday, December 16, 2009 9:39 AM
> >To: Johan De Meersman
> >Cc: Gavin Towey; mysql@stripped
> >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 <vegivamp@stripped
> >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 <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