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
