I have a reply for both Cal and Javier, Cal, I have a hunch the JOIN is backward for you.
> 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 That should produce all the interest_id's in member_interests, and the count of the records in interests which match (which would be 0 or 1). Doesn't sound like what you want. Compare it to: SELECT i.interest_id, COUNT(*) AS count, i.name FROM interests AS i left join member_interests AS mi on i.id = mi.interest_id GROUP BY mi.interest_id, i.name ORDER BY i.name This query should show all records of interests, as well as a count of the number of members interested. Javier, In order to filter your query based on the count(*), you must use the HAVING clause, which is executed after the GROUP BY. It sounds like you want: <query> SELECT 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> That query will show all mi.interest_id values, and the number of (BTW to all: It's unnecessary, and possibly misleading, to use DISTINCT as you have, in this query. The GROUP BY makes sure tou only get one row with any distinct mi.interest_id .) > -----Original Message----- > From: Javier Campoamor [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, June 05, 2002 12: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
