As a first time poster, you should learn to follow forum etiquette and read up on posting questions with DDL and consumable sample data. There are many links you can find this information on. As it is Christmas and I'm feeling charitable, I'll help you out this time.

I'm not going to promise that the following will be any faster than your JOIN, but it might be, or at the very least is another alternative to try.

-- DDL

CREATE TABLE #ApartmentSwaps (Id1 INT, Id2 INT

,PRIMARY KEY (id1, id2))

-- Consumable sample data

INSERT INTO #ApartmentSwaps

SELECT 1, 3 UNION ALL SELECT 1, 2 UNION ALL SELECT 1, 4

UNION ALL SELECT 1, 7 UNION ALL SELECT 2, 5 UNION ALL SELECT 2, 9

UNION ALL SELECT 3, 1 UNION ALL SELECT 3, 2 UNION ALL SELECT 5, 1

UNION ALL SELECT 9, 1

DECLARE @MyID INT = 1

SELECT id1, id2, id3

FROM #ApartmentSwaps

CROSS APPLY (SELECT id3=id1 FROM #ApartmentSwaps WHERE id2 = @MyID) a

WHERE id1 = @MyID AND id1 <> id2 AND id2 <> id3 AND id1 <> id3

-- Always use temp tables in your questions and remember to drop them

DROP TABLE #ApartmentSwaps

Note how I specified a PRIMARY KEY on the table, which creates a CLUSTERED INDEX. This should help the query perform better.

You could also do this with a set-based WHILE loop, and I've seen cases where that beats out the JOIN and CROSS APPLY attacks to this kind of a problem.

Also, beware of parameter-sniffing the way I've written the query (using @MyID). You may also want to try the same query with OPTION (RECOMPILE) to see if it runs any faster.