Count records in join

--0015175d0750f87f29047ac8c047
Content-Type: text/plain; charset=ISO-8859-1

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

--0015175d0750f87f29047ac8c047--

RE: Count records in join

Hi Miguel,

You'll need to use LEFT JOIN, that will show all records that match and a r=
ow 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@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 th=
at reviewing, disseminating, disclosing, copying or distributing this e-mai=
l is strictly prohibited. Please notify the sender immediately by e-mail i=
f 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-fre=
e as information could be intercepted, corrupted, lost, destroyed, arrive l=
ate 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 transmis=
sion. [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=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Count records in join

--0050450181028e8909047ad8c1cd
Content-Type: text/plain; charset=ISO-8859-1

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 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@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=vegivamp@tuxera.be
>
>

--0050450181028e8909047ad8c1cd--

Re: Count records in join

--000e0cd1e2223222fb047ad977f1
Content-Type: text/plain; charset=ISO-8859-1

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 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 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@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=vegivamp@tuxera.be
>>
>>
>

--000e0cd1e2223222fb047ad977f1--

RE: Count records in join

>-----Original Message-----
>From: Miguel Vaz [mailto:pagongski@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 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 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@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=vegivamp@tuxera.be
>>>
>>>
>>




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Count records in join

--0015175d039863f842047ada247b
Content-Type: text/plain; charset=ISO-8859-1

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
wrote:

> >-----Original Message-----
> >From: Miguel Vaz [mailto:pagongski@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 > >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 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@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=vegivamp@tuxera.be
> >>>
> >>>
> >>
>
>
>
>

--0015175d039863f842047ada247b--