Getting sequential field from another based on Sequence from another table

Question

I have two table Table1

SETID  CRSE_ID COMPONENT CAMPUS 
A      10001   LAB        C1
A      10002   LEC        C1
A      10003   LAB        C1
A      10004   LAB        C1

Table2

SETID  ITEM TYPE      DESCR   CAMPUS
A      300000220010   Book    C1
A      300000220020   Book    C2
A      300000220030   Book    C1
A      300000220040   Book    C2
A      300000220050   Book    C1
A      300000220060   Book    C2
A      300000220070   Book    C1

I want to get a result where Item Types are mapped to each row

SETID  CRSE_ID COMPONENT CAMPUS ITEM_TYPE
A      10001   LAB        C1    300000220010   
A      10001   LEC        C1    300000220030   
A      10001   LAB        C1    300000220050   
A      10001   LAB        C1    300000220070 
A      10002   LAB        C1    300000220010   
A      10002   LEC        C1    300000220030   
A      10002   LAB        C1    300000220050   
A      10002   LAB        C1    300000220070
A      10003   LAB        C1    300000220010   
A      10003   LEC        C1    300000220030   
A      10003   LAB        C1    300000220050   
A      10003   LAB        C1    300000220070
A      10004   LAB        C1    300000220010   
A      10004   LEC        C1    300000220030   
A      10004   LAB        C1    300000220050   
A      10004   LAB        C1    300000220070   

The Sequence of Item Type keeps increasing with when new CRSE_ID. The Item Types end with Odd for Campus C1

Answer 1

I think you simply want to add a sequential number and use that as one of the join conditions:

select t1.*, t2.item_type
from (select t1.*, row_number() over (partition by setid, campus order by setid) as seqnum
      from table1 t1
     ) t1 left join
     (select t2.*, row_number() over (partition by setid, campus order by setid) as seqnum
      from table2 t2
     ) t2
     on t1.setid = t2.setid and t1.campus = t2.campus and t1.seqnum = t2.seqnum;