Re: Filtering join - Eris Ristemena - com.mysql.lists.mysql

26 messages in com.mysql.lists.mysqlRe: Filtering join

From	Sent On	Attachments
Grant Giddens	20 Dec 2005 19:37
James Harvard	21 Dec 2005 03:19
Grant Giddens	21 Dec 2005 05:50
Hank	21 Dec 2005 06:10
Grant Giddens	21 Dec 2005 06:17
Eris Ristemena	21 Dec 2005 06:19
James Harvard	21 Dec 2005 06:25
Hank	21 Dec 2005 06:39
SGr...@unimin.com	21 Dec 2005 07:00
Gleb Paharenko	21 Dec 2005 07:40
Peter Brawley	21 Dec 2005 07:43
Eris Ristemena	21 Dec 2005 07:53
Eris Ristemena	21 Dec 2005 08:01
SGr...@unimin.com	21 Dec 2005 08:11
Eris Ristemena	21 Dec 2005 09:14
SGr...@unimin.com	21 Dec 2005 09:31
Eris Ristemena	21 Dec 2005 09:42
Grant Giddens	21 Dec 2005 09:57
Hank	21 Dec 2005 10:33
Grant Giddens	21 Dec 2005 10:44
Grant Giddens	21 Dec 2005 14:10
Hank	22 Dec 2005 16:07
Grant Giddens	23 Dec 2005 06:45
James Harvard	23 Dec 2005 08:17
Grant Giddens	23 Dec 2005 08:49
Hank	23 Dec 2005 15:03

Subject:	Re: Filtering join
From:	Eris Ristemena (er...@ristemena.com)
Date:	12/21/2005 08:01:37 AM
List:	com.mysql.lists.mysql

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

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.

-----

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