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
>
>
>
