CODEWITHSUNDEEP
sqlsql-server
theo
theo

Reputation: 299

SQL Server - How to SELECT values from different rows but in the same table

So for example I have this table called tb_Item

----------------------------------
|Item_Ref|Main_Item_Ref|Type|Rate|
|--------+-------------+----+----|
|1234ABC |MNJDH        |Sub |0   |
|MNJDH   |MNJDH        |Main|98  |
----------------------------------

Basically, what I want to achieve is to get the Rate value of the Main item and put it on Sub Item/s.

In this sample table, how can item 1234ABC get the 98 rate from its main item MNJDH and return row like this:

----------------------------------
|Item_Ref|Main_Item_Ref|Type|Rate|
|--------+-------------+----+----|
|1234ABC |MNJDH        |Sub |98  |
----------------------------------

Thank you.

Upvotes: 0

Views: 54

Answers (2)

RoMEoMusTDiE
RoMEoMusTDiE

Reputation: 4824

Self inner join

 select t1.Item_Ref, t1.Main_Item_ref, t1.type,t2.Rate
  from #temptable t1
  inner join #temptable t2 
    on t1.Main_Item_ref = t2.Main_Item_ref 
    and t2.type = 'Main'
    and t1.type ='Sub'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You can do this with a self-join:

select i.*, m.Rate
from tb_item i left join
     tb_item m
     on i.main_item_ref = m.main_item_ref and
        m.type = 'Main'
where i.type = 'Sub';

Upvotes: 1

Related Questions