Try this.
<query>
SELECT DISTINCT(mi.interest_id), COUNT(*) AS count,
i.name
FROM member_interests AS mi left join outer interests AS i on
mi.interest_id = i.id
GROUP BY mi.interest_id
HAVING count < 5
ORDER BY i.name
</query>
=C=
*
* Cal Evans
* Journeyman Programmer
* Techno-Mage
* http://www.calevans.com
*
-----Original Message-----
From: Javier Campoamor [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, June 05, 2002 2:52 AM
To: [EMAIL PROTECTED]
Subject: RE: Left join?
Hi,
I have a similar problem (with phones & calls) but I would like to select
only those elements that have less than a number.
Using the previous example, is like selecting only those interest elements
that have less that 5 members associated.
Something like this (obviously this doesn't work)
<query>
SELECT DISTINCT(mi.interest_id), COUNT(*) AS count, i.name
FROM member_interests AS mi left join outer interests AS i on
mi.interest_id = i.id
AND count < 5
GROUP BY mi.interest_id
ORDER BY i.name
</query>
Does anyone have an idea to solve this kind of problem? Does anyone know
where can I find a web site with complex queries?
Thank you
Javier
> -----Mensaje original-----
> De: Cal Evans [mailto:[EMAIL PROTECTED]]
> Enviado el: martes 4 de junio de 2002 22:48
> Para: [EMAIL PROTECTED]; [EMAIL PROTECTED]
> Asunto: RE: Left join?
>
>
> SELECT DISTINCT(mi.interest_id), COUNT(*) AS count,
> i.name
> FROM member_interests AS mi left join outer interests AS i on
> mi.interest_id = i.id
> GROUP BY mi.interest_id
> ORDER BY i.name
>
> You are correct, a left outer join is what you need.
>
>
> *
> * Cal Evans
> * Journeyman Programmer
> * Techno-Mage
> * http://www.calevans.com
> *
>
>
> -----Original Message-----
> From: Daren Cotter [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, June 04, 2002 2:07 PM
> To: [EMAIL PROTECTED]
> Subject: Left join?
>
>
> I have the following tables:
>
> Member_interests:
> Member_id
> Interest_id
>
> Interests:
> Name
> Interest_id
>
> I need a query that selects each interest name, and the # of members who
> have selected it...sample output:
> Boating 25
> Hiking 10
> ..
> Swimming 0
> Jumping 0
> Talking 0
>
> The following query works great, but does not display the Interest names
> with 0 members:
> SELECT DISTINCT(mi.interest_id), COUNT(*) AS count, i.name FROM
> member_interests AS mi, interests AS i WHERE mi.interest_id = i.id GROUP
> BY mi.interest_id ORDER BY i.name
>
> Is there a way to have the "0"'s displayed as well? I'm thinking a left
> join would probably be involved?
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
>
> ---------------------------------------------------------------------
> Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
> <[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
