List:General Discussion« Previous MessageNext Message »
From:Eris Ristemena Date:December 21 2005 4:01pm
Subject:Re: Filtering join
View as plain text  
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
>
>
>

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