Hypothetical database for events happening around the world.
EVENT
event_id | event_name
1 | Great Wall Party
2 | Times Square Dance
3 | Sydney Blowout
PLACE
place_id | place_name
54 | Times Square
55 | Manhattan
56 | New York City
57 | New York State
58 | USA
EVENTPLACE
eventid | placeid
2 | 54
RELATEDPLACES
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!
