Case on Join condition

I have a two tables data as below

table1:
Order    orderitem    value    
 O1       B-IV122       10
 O2       B-IV144       10     

table2:
order    Productionorder    productionitem    ProductionValue
 O1         P1               B-IV122               5
 O2         P2               B-IV111               6
 O2         P2               CCC144                6
 O2         P2               CCC000                4

Required Output:

  Order     Productionorder  orderitem   productionitem  value   ProductionValue
    O1         P1             B-IV122     B-IV122         10       5
    O2         P2             B-IV144     B-IV111         10       6

I tried with below code

select order,orderitem,Productionorder,productionitem,value ,ProductionValue  from 
(select order,orderitem,value from table1) t1
left outer join
(select Order,Productionorder,productionitem,ProductionValue from table2)t2
on t1.order = t2.order and t1.orderitem = t2.productionitem

MY query output:

 Order     Productionorder  orderitem   productionitem  value   ProductionValue
    O1         P1             B-IV122     B-IV122         10       5
    O2         P2             B-IV144     NULL            10       NULL

Instead of null,i want to take production item (B-IV111) which starts with ‘B’ and respective value.. (Note: I need to make a join with order and item too).Case is not working as expected.Can you please suggest me on this.

Thanks in advance.

Answer

SELECT t1.[order]
    ,orderitem
    ,value
    ,productionitem
    ,productionvalue
FROM table1 t1
INNER JOIN table2 t2 ON t1.[order] = t2.[order]
    AND LEFT(t1.orderitem, 3) = LEFT(t2.productionitem, 3)

Advertisement

Answer