Hello
> id name no id cust
> 1 a 2 1 y
> 2 b null null null
> 3 c null null null
Obviously this query should be redesigned, however it does produce
results which you want:
select id
, name
, no
, cust
from
(select *
from t1 left join t2 using (id)) as t3
where no = (
select max(no)
from (select *
from t1 left join t2 using (id)) as t3)
or no is null;
+------+------+------+------+
| id | name | no | cust |
+------+------+------+------+
| 1 | a | 2 | y |
| 2 | b | NULL | NULL |
| 3 | c | NULL | NULL |
+------+------+------+------+
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
>
>
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Gleb Paharenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET
<___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
