List:General Discussion« Previous MessageNext Message »
From:Kevin Fries Date:June 5 2002 7:23pm
Subject:RE: Left join?
View as plain text  
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:cal@stripped]
> Sent: Wednesday, June 05, 2002 11:55 AM
> To: kfries@stripped; 'Javier Campoamor'; mysql@stripped
> 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:kfries@stripped]
> Sent: Wednesday, June 05, 2002 11:10 AM
> To: 'Javier Campoamor'; mysql@stripped
> 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:jcampoamor@stripped]
> > Sent: Wednesday, June 05, 2002 12:52 AM
> > To: mysql@stripped
> > 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:cal@stripped]
> > > Enviado el: martes 4 de junio de 2002 22:48
> > > Para: mysql@stripped; mysql@stripped
> > > 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:mysql@stripped]
> > > Sent: Tuesday, June 04, 2002 2:07 PM
> > > To: mysql@stripped
> > > 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
> <mysql-thread110982@stripped>
> > > To unsubscribe, e-mail
> > > <mysql-unsubscribe-cal=calevans.com@stripped>
> > > 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
> <mysql-thread110995@stripped>
> > > To unsubscribe, e-mail
> > > <mysql-unsubscribe-jcampoamor=lapize.com@stripped>
> > > 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 <mysql-thread111068@stripped>
> To unsubscribe, e-mail
> <mysql-unsubscribe-cal=calevans.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>
>

Thread
Left join?Daren Cotter4 Jun
  • RE: Left join?Cal Evans4 Jun
    • RE: Left join?Javier Campoamor5 Jun
      • RE: Left join?Cal Evans5 Jun
      • RE: Left join?Kevin Fries5 Jun
        • RE: Left join?Cal Evans5 Jun
          • RE: Left join?Kevin Fries5 Jun