Create loop within SQL SELECT statement until chain broken

Hypothetical database for events happening around the world.

event_id | event_name
1 | Great Wall Party
2 | Times Square Dance
3 | Sydney Blowout

place_id | place_name
54 | Times Square
55 | Manhattan
56 | New York City
57 | New York State
58 | USA

eventid | placeid
2 | 54

rel_placeid1 | rel_placeid2
54 | 55
55 | 56
56 | 57
57 | 58

If I display the event, Times Square Dance, I’d like to display all the places that appear up the chain of its associated places via the RELATEDPLACES table (i.e. Times Square, Manhattan, New York City, New York State, USA). Likewise, if I call all events for USA, I’d like the Times Square dance to be listed, given its EVENTPLACE (Times Square) appears at the bottom of the RELATEDPLACES chain of associations starting with USA.

I think I need to create an inner loop within my SQL command so that it keeps performing until there is a break in the chain. So far (using the first of the two above examples) I have:-

SELECT place_nm FROM eventplace
INNER JOIN relatedplaces ON placeid = rel_placeid1
INNER JOIN place ON rel_placeid2 = place_id
[**where the loop should begin:
INNER JOIN relatedplaces ON place_id = rel_placeid1
INNER JOIN place ON rel_placeid2 = place_id
end loop**]
WHERE eventid = ‘2’;

This is complicated by the fact that I need different table aliases for each loop, which means I can’t state in the opening SELECT statement that I want to be collecting all the place_name data in the same column.

I’m not sure if I what I am trying to achieve is even possible and my current fallback solution is to list all of Times Square’s related places in the RELATEDPLACES table, rather than just the next largest place (Manhattan), but this seemed like the better solution (and would also save database space).

Can anyone suggest the SQL SELECT command I might need to use? Cheers!

there are no loops in sql :slight_smile:

that said, you did not mention which database you’re using, so let me suggest you search for recursive CTE, with the proviso that your database might not support it

Thanks Rudy - I did not think there would necessarily be a simple answer on this one. Will look into your suggestion, which is new territory for me (I always feel a little humbled in your presence).