MySQL 5.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


5.5.62


CREATE TABLE room_map (id INT NOT NULL AUTO_INCREMENT, 
room_num INT, target_room INT,  PRIMARY KEY (id));
/*555 -> 444 -> 99 -> 332 -> 101 -> 817 -> 761 -> 123;*/
INSERT INTO room_map(room_num, target_room) VALUES 
(555,444),(555,13),(555, 29),(444,555),(444,99),(444, 1310),
(99,332),(332,88),(332,101),(101, 100),(101,817),(817,761),
(817, 827),(761,123),(555,11),(555,761),(555, 90),(99,123),
(817,101),(332,444),(332,11),(332,10);
select * from room_map;


id
room_num
target_room


1
555
444

2
555
13

3
555
29

4
444
555

5
444
99

6
444
1310

7
99
332

8
332
88

9
332
101

10
101
100

11
101
817

12
817
761

13
817
827

14
761
123

15
555
11

16
555
761

17
555
90

18
99
123

19
817
101

20
332
444

21
332
11

22
332
10


SET @room := 555; -- setting variable for first room
SET @troom := 123; -- setting variable for last room

SELECT r1.room_num, r1.target_room, r2.target_room, r3.target_room, r4.target_room, r5.target_room, r6.target_room, r7.target_room
FROM room_map r1 
JOIN room_map r2 ON r1.target_room=r2.room_num
JOIN room_map r3 ON r2.target_room=r3.room_num
JOIN room_map r4 ON r3.target_room=r4.room_num
JOIN room_map r5 ON r4.target_room=r5.room_num
JOIN room_map r6 ON r5.target_room=r6.room_num
JOIN room_map r7 ON r6.target_room=r7.room_num
WHERE r1.room_num=@room -- using first room variable
AND r7.target_room=@troom -- using last room variable 
;


room_num
target_room
target_room
target_room
target_room
target_room
target_room
target_room


555
444
99
332
101
817
761
123

555
444
99
332
444
555
761
123

555
444
555
444
555
444
99
123

id	room_num	target_room
1	555	444
2	555	13
3	555	29
4	444	555
5	444	99
6	444	1310
7	99	332
8	332	88
9	332	101
10	101	100
11	101	817
12	817	761
13	817	827
14	761	123
15	555	11
16	555	761
17	555	90
18	99	123
19	817	101
20	332	444
21	332	11
22	332	10

room_num	target_room	target_room	target_room	target_room	target_room	target_room	target_room
555	444	99	332	101	817	761	123
555	444	99	332	444	555	761	123
555	444	555	444	555	444	99	123

room_num	target_room	target_room	target_room	target_room	target_room	target_room	target_room
555	444	99	332	101	817	761	123
555	444	99	332	444	555	761	123
555	444	555	444	555	444	99	123

room_num	target_room	target_room	target_room	target_room	target_room	target_room	target_room
555	444	99	332	101	817	761	123
555	444	99	332	444	555	761	123
555	444	555	444	555	444	99	123