I'd correct my post to recommend:
SELECT i.interest_id, COUNT(mi.interest_id) AS count, i.name
FROM interests AS i left join member_interests AS mi
on i.interest_id = mi.interest_id
GROUP BY i.interest_id, i.name
ORDER BY i.name
My original group-by was wrong, as I was referring to the possibly-null
mi.interest_id.
I'll be interested in hearing which direction turns out to be correct.
Here's an example layout,
which you might want to correct me on:
CREATE TABLE interests (
interest_id int(11) default NULL,
name varchar(30) default NULL
) TYPE=MyISAM;
INSERT INTO interests VALUES (1,'basketball');
INSERT INTO interests VALUES (2,'yoga');
INSERT INTO interests VALUES (3,'gardening');
INSERT INTO interests VALUES (4,'doing taxes');
CREATE TABLE member_interests (
member_id int(11) default NULL,
interest_id int(11) default NULL
) TYPE=MyISAM;
INSERT INTO member_interests VALUES (1,1);
INSERT INTO member_interests VALUES (2,1);
INSERT INTO member_interests VALUES (3,1);
INSERT INTO member_interests VALUES (1,2);
INSERT INTO member_interests VALUES (2,2);
INSERT INTO member_interests VALUES (3,3);
SELECT i.interest_id, COUNT(mi.interest_id) AS count, i.name
FROM interests AS i left join member_interests AS mi
on i.interest_id = mi.interest_id
GROUP BY i.interest_id, i.name
ORDER BY i.name
+-------------+-------+-------------+
| interest_id | count | name |
+-------------+-------+-------------+
| 1 | 3 | basketball |
| 4 | 0 | doing taxes |
| 3 | 1 | gardening |
| 2 | 2 | yoga |
+-------------+-------+-------------+
This report shows that no one is interested in doing taxes. ;-)
hth,
Kevin
> -----Original Message-----
> From: Cal Evans [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, June 05, 2002 11:55 AM
> To: [EMAIL PROTECTED]; 'Javier Campoamor'; [EMAIL PROTECTED]
> Subject: RE: Left join?
>
>
> You could be correct, but I don't think so. The question was count the
> members and give me the description. Just by reading it, I
> don't think your
> query will answer that question.
>
>
> I would however drop the distinct because the group by will
> do that for you.
>
> > 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
> > ORDER BY i.name
>
> =C=
>
> *
> * Cal Evans
> * Journeyman Programmer
> * Techno-Mage
> * http://www.calevans.com
> *
>
>
> -----Original Message-----
> From: Kevin Fries [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, June 05, 2002 11:10 AM
> To: 'Javier Campoamor'; [EMAIL PROTECTED]
> Subject: RE: Left join?
>
>
> 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
>
>
>
---------------------------------------------------------------------
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
