Compare two table columns,

Question

Compare two table column ,if Item_Rate of B_ID against Level_Four_ID exit in #tbl_Assign ,then item_Rate Pick from #tbl_Assign,if Item_Rate does not exit in #tbl_Assign of B_ID, then rate will pick from #tbl_bottles .
I Created below condition ,in which i defined B_ID=2,which is not exit in #tbl_Assign ,but exit in #tbl_bottles,then rate will pick from #tbl_bottles.but my query is not taking ouput

    Create table #tbl_bottles(B_ID int,B_Name varchar(50),Rate int)
    Create table #tbl_Account_L_Four (Level_Four_ID int,Level_Four_Name varchar(50),Opening decimal(10,2),Opening_Date date)
    Create table #tbl_Assign (Assign_ID int,Level_Four_ID int,B_ID int,item_Rate int)


    INSERT INTO #tbl_Account_L_Four VALUES(1231,'Abdul Rauf',60000,null)
    INSERT INTO #tbl_Account_L_Four VALUES(1222,'Cheque In Hand',45000,'2021-01-17')
    INSERT INTO #tbl_Account_L_Four VALUES(1215,'MBL 833968',0,null)

    insert into #tbl_bottles VALUES(1,'A',50)
    insert into #tbl_bottles VALUES(2,'B',60)
    insert into #tbl_bottles VALUES(3,'C',70)

    insert into #tbl_Assign VALUES(11,1231,1,150)
    insert into #tbl_Assign VALUES(12,1231,3,350)

    select Isnull(A.Item_Rate,B.rate) as rate From #tbl_bottles B left join #tbl_Assign A on A.B_ID=B.B_ID 
where  B.B_ID = 2 and A.Level_Four_ID=1231

Answer 1

Hi @Analyst_SQL ,

Please try the following. Thanks!

SELECT CASE WHEN ass.item_Rate IS NOT NULL THEN ass.item_Rate ELSE btl.Rate END AS rate  
FROM #tbl_Account_L_Four AS acc  
  CROSS JOIN  #tbl_bottles AS btl  
  LEFT JOIN #tbl_Assign AS ass ON(ass.Level_Four_ID = acc.Level_Four_ID AND ass.B_ID = btl.B_ID)  
WHERE acc.Level_Four_ID = 1231 AND btl.B_ID = 2  

----------

If the above response is helpful, please "Accept as answer" and "Up-vote" it. Thanks!