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/[EMAIL PROTECTED]
