List:General Discussion« Previous MessageNext Message »
From:Eris Ristemena Date:December 21 2005 5:15pm
Subject:Re: Filtering join
View as plain text  
Ok, that's easy, i see. But now how can i also get who hasn't done the
expense?

person
id        name
1         james
2         michael
3         jack

expense
no        id        exp
1          1        2000
2          2        1000
3          1          500

so that the result should be:

id      name      no        exp
1       james      3         500
2       michael    2       1000
3       jack        null       null




----- Original Message -----
From: <SGreen@stripped>
To: "Eris Ristemena" <eris@stripped>
Cc: <mysql@stripped>
Sent: Wednesday, December 21, 2005 11:11 PM
Subject: Re: Filtering join


> I can't take it any longer. Come on list this is an easy one!! We have
> only had this question asked about once every other week this year.  Shame
> on you lurkers who knew the answer but didn't kick in.  8-(
>
> Eris,
> What you are looking for can be called the "group wise maximum" because
> you want the row with the max value for a particular group. In your case
> (using your second example) you want the row with the maximum `no` for
> each `id` value
>
> The FINE MANUAL has three ways to write queries to solve this problem:
>
http://dev.mysql.com/doc/refman/4.1/en/example-maximum-column-group-row.html
>
> Let us know if you have problems translating the documentation to fit your
> needs. (for shame, for shame!)
>
> Merry Christmas!
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
>
>
> "Eris Ristemena" <eris@stripped> wrote on 12/21/2005 11:01:59 AM:
>
> > Ok, i change the table to make it clear.
> >
> > I have two tables, person and expense
> > person
> > id        name
> > 1         james
> > 2         michael
> >
> > expense
> > no        id        exp
> > 1          1        2000
> > 2          2        1000
> > 3          1          500
> >
> > where expense.no is an autoincrement column.
> >
> > how can i get the last expense of each people in table person? so the
> result
> > should be:
> >
> > id      name      no        exp
> > 1       james      3         500
> > 2       michael    2       1000
> >
> > thanks in advance
> >
> >
> >
> >
> > ----- Original Message -----
> > From: "Peter Brawley" <peter.brawley@stripped>
> > To: "Eris Ristemena" <eris@stripped>
> > Cc: <mysql@stripped>
> > Sent: Wednesday, December 21, 2005 10:43 PM
> > Subject: Re: Filtering join
> >
> >
> > > Eris,
> > >
> > >  >...what i need is a distinct t1.id with maximum t2.no, so that the
> > result
> > >  >should be like this:
> > >  >id name no id cust
> > >  >1 a 2 1 y
> > >  >2 b null null null
> > >  >3 c null null null
> > >
> > > To get the maximum t2.no value for each t1.id value, try ...
> > >
> > >   SELECT
> > >     t1.id,
> > >     t1.name,
> > >     t2.id,
> > >     MAX(t2.no)
> > >   FROM t1 LEFT JOIN t2 USING (id)
> > >   GROUP BY t1.id;
> > >
> > > but because of the MAX() / GROUP BY aggregation, adding t2.cust to the
> > > query will not give you the t2.cust values that go with t2.no values.
> > >
> > > PB
> > >
> > > -----
> > >
> > > Eris Ristemena wrote:
> > >
> > > >hi all,
> > > >
> > > >i have this small problem. I hope someone can help me out here.
> > > >
> > > >i have two table with one-to-many relations,
> > > >t1
> > > >id   name
> > > >1      a
> > > >2      b
> > > >3      c
> > > >
> > > >t2
> > > >no   id   cust
> > > >1      1     x
> > > >2      1     y
> > > >
> > > >using join statement like this:
> > > >select * from t1 left join t2 using (id)
> > > >
> > > >i get this result:
> > > >id    name   no   id   cust
> > > >1        a       1     1     x
> > > >1        a       2     1     y
> > > >2        b      null  null  null
> > > >3        c      null  null  null
> > > >
> > > >but what i need is a distinct t1.id with maximum t2.no, so that the
> > result
> > > >should be like this:
> > > >id    name   no   id   cust
> > > >1        a       2     1     y
> > > >2        b      null  null  null
> > > >3        c      null  null  null
> > > >
> > > >Can someone help me how? group by seem doesn't work.
> > > >
> > > >regards,
> > > >-ers
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > > --
> > > No virus found in this outgoing message.
> > > Checked by AVG Free Edition.
> > > Version: 7.1.371 / Virus Database: 267.14.3/209 - Release Date:
> 12/21/2005
> > >
> > >
> > >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> >
>

Thread
Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
Re: Do I need to tweak my server variables for this SELECTstatement?James Harvard21 Dec
  • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
    • Re: Do I need to tweak my server variables for this SELECT statement?Hank21 Dec
      • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
        • Re: Do I need to tweak my server variables for this SELECT statement?Hank21 Dec
          • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
          • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
            • Re: Do I need to tweak my server variables for this SELECT statement?Hank23 Dec
              • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens23 Dec
                • Re: Do I need to tweak my server variables for this SELECTstatement?James Harvard23 Dec
                  • Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens23 Dec
                    • Re: Do I need to tweak my server variables for this SELECT statement?Hank24 Dec
    • Filtering joinEris Ristemena21 Dec
      • Re: Filtering joinPeter Brawley21 Dec
      • Re: Filtering joinGleb Paharenko21 Dec
    • Re: Do I need to tweak my server variables for this SELECTstatement?James Harvard21 Dec
    • Re: Filtering joinEris Ristemena21 Dec
    • Re: Filtering joinEris Ristemena21 Dec
      • Re: Filtering joinSGreen21 Dec
        • Re: Filtering joinEris Ristemena21 Dec
          • Re: Filtering joinSGreen21 Dec
            • Re: Filtering joinEris Ristemena21 Dec
Re: Do I need to tweak my server variables for this SELECT statement?Grant Giddens21 Dec
  • Re: Do I need to tweak my server variables for this SELECT statement?Hank21 Dec
    • Re: Do I need to tweak my server variables for this SELECT statement?SGreen21 Dec