Here are two ways that work with your sample data. Also, I think it'll continue to work if you add mroe data: DECLARE @TableA TABLE (ID INT, [Name] VARCHAR(10))INSERT @TableA SELECT 1, 'Test1'UNION ALL SELECT 2, 'Test2'UNION ALL SELECT 3, 'Test3'DECLARE @TableB TABLE (ID INT, Item VARCHAR(10))INSERT @TableBSELECT 1, 'Item1'UNION ALL SELECT 2, 'Item2'DECLARE @TableC TABLE (ID INT, AID INT, BID INT) INSERT @TableC SELECT 1, 1, 1-- Option 1 (*should* be more effecient)SELECT DISTINCT A.ID, A.Name, CASE WHEN C.BID IS NOT NULL THEN B.Item ELSE NULL END AS ItemFROM @TableA AS ACROSS JOIN @TableB AS BLEFT OUTER JOIN @TableC AS C ON A.ID = C.AID AND B.ID = C.BIDLEFT OUTER JOIN @TableB AS B1 ON C.BID = B1.ID-- Option 2 with Derived TableSELECT T.ID, T.Name, B.ItemFROM @TableB AS BRIGHT OUTER JOIN ( SELECT DISTINCT A.ID, A.Name, C.BID FROM @TableA AS A CROSS JOIN @TableB AS B LEFT OUTER JOIN @TableC AS C ON A.ID = C.AID AND B.ID = C.BID ) AS T ON B.ID = T.BID
-Ryan