Post reply

join one-to-many but return latest one

  • December 11, 2008 at 7:22 am

    #155012

    Hi,

    I have 2 table with one-to-many relationship

    Table1

    ID Value1 Value2 ....

    1 a b

    2 a1 b1

    3 a2 b2

    ....

    Table2

    ID Date1 Value3 ...

    1 09/Nov/2008 a

    1 09/Jul/2008 b

    2 09/May/2008 c

    3 09/Jan/2008 d

    2 07/Jun/2008 e

    ....

    How I can get the result like

    ID Date1 Value1 Value2 Value3....

    1 09/Nov/2008 a b a

    2 07/Jun/2008 a1 b1 e

    3 09/Jan/2008 a2 b2 d

    ...

    Thank you,

    Wes

  • December 11, 2008 at 7:32 am

    #911066

    Something like this

    select t1.ID, t1.Date1,t1.Value1,t1.Value2,t2.Value3 from table1 as t1

    inner join

    (

    select id,value3 from table2 where date1=(select max(date1) from table2 where id=t1.id)

    ) as t2

    on t1.id=t2.id

    Madhivanan

    Failing to plan is Planning to fail

  • December 11, 2008 at 8:10 am

    #911109

    Thank you, it works like charming

    Wes

  • December 11, 2008 at 8:17 am

    #911114

    You might want to try using TOP(1) with an ORDER BY. If you're referencing an index, the TOP (1) is pretty consistently faster than the MAX.

    ----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • December 16, 2008 at 3:49 am

    #913023

    weslleywang (12/11/2008)

    Thank you, it works like charming

    Wes

    You are welcome 🙂

    Madhivanan

    Failing to plan is Planning to fail

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply