From: Peter Brawley
Date: December 21 2005 3:43pm
Subject: Re: Filtering join
List-Archive: http://lists.mysql.com/mysql/193105
Message-Id: <43A9782E.10704@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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