T-SQL : Sub Query to Join

Question

I have a select statement which has multiple selects inside a select and it is taking forever to execute because of the huge volume of the data , below is the query and the table structures, can I help modifying in to select with joins instead of sub query in the main select.

--Tables:  
   create table #dispense  
(  
  primarykeydispenseid int  
  ,ForeignKeyDispenseID int  
  ,ForeignKeyCodeListDistTypeID int  
)  
  
create table #Receive  
(  
  PrimaryKeyReceiveID int  
  ,ForgienKeyProtocolID int   
)  
create table #transfer  
(  
  PrimaryKeyTransferID int  
  ,ForeignKeyReceiveID int  
)  
create table #CodeList  
(  
  PrimaryKeyCodeListID int  
  ,CodeListParentCD varchar(20)  
  ,CodeListCD varchar(20)  
)  
--Query:  
  
select (  
SELECT ForgienKeyProtocolID  
FROM #Receive  
WHERE PrimaryKeyReceiveID = CASE disp.ForeignKeyCodeListDistTypeID  
WHEN (  
SELECT DISTINCT  PrimaryKeyCodeListID  
FROM #CodeList  
WHERE CodeListParentCD = 'transtype'  
AND CodeListCD = 'receive'  
)  
THEN DISP.ForeignKeyDispenseID  
ELSE (  
SELECT ForeignKeyReceiveID  
FROM #transfer  
WHERE PrimaryKeyTransferID = DISP.ForeignKeyDispenseID  
)  
END ) as acc  
from #dispense DISP  

Answer 1

You can rewrite your query with JOINs based on their relationship instead of this correlated query. You need to show your table relationship and better with some sample data and expected result.