Post reply

join on tables

  • August 12, 2010 at 12:11 am

    #103973

    hi,

    I have two tables like

    create table a(id int, expense int)

    insert into a

    select 1,2000

    union all

    select 1,2000

    union all

    select 2,3000

    union all

    select 2,3000

    create table b(id int, amount int)

    insert into b

    select 1,5000

    union all

    select 2,10000

    i write the query ---

    select

    a.id , a.expense

    ,b.id , b.amount

    from a

    inner join b on b.id = a.id

    it return the result -----

    1200015000

    1200015000

    23000210000

    23000210000

    but i want -------------

    1200015000

    1200010

    23000210000

    2300020

    please help me

    Thanks

  • August 12, 2010 at 3:54 am

    #1205886

    You can use a ROW_NUMBER() for the same. Have a look at it in Books Online.

    SELECTa_id, expense, b_id, CASE WHEN RowNum = 1 THEN amount ELSE 0 END amount

    FROM(

    SELECTROW_NUMBER() OVER( PARTITION BY b.id, b.amount ORDER BY b.id ) RowNum,

    a.id a_id, a.expense, b.id b_id, b.amount

    FROMa

    INNER JOIN b ON b.id = a.id

    ) T

    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • August 12, 2010 at 4:29 am

    #1205901

    Thanks Champ,

  • August 12, 2010 at 4:46 am

    #1205908

    Actually you are required to join the row number using Row_Number() function and not based on any other field

  • August 12, 2010 at 4:49 am

    #1205909

    NOW I UNDERSTAND THE LOGIC BEHIND IT.

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

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