Is this what you are looking for?;with cte as( SELECT SalesForce.DailyAccounts.[Account_ID], SalesForce.DailyAccounts.MID_Internal, Dim_Outlet.Agent_Chain_No FROM SalesForce.DailyAccounts INNER JOIN Dim_Outlet ON SalesForce.DailyAccounts.MID_Internal = Dim_Outlet.FDMSAccountNo )select a.[Account_ID], a.MID_Internal, a.Agent_Chain_No, b.[Account_ID] as testfrom cte a left join cte b on a.Agent_Chain_No = Agent_Chain_No.MID_Internal;