Compare two table columns,

akhter hussain 3,001 Reputation points

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
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
9,795 questions
0 comments No comments
{count} votes

Accepted answer
  1. Nasreen Akter 10,656 Reputation points

    Hi @akhter hussain ,

    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!

    1 person found this answer helpful.
    0 comments No comments

0 additional answers

Sort by: Most helpful