I always start with getting the right data design and SQL to make sure I have the query right so it would work in any environment. Getting the right data design is key (no pun intended) and can really take the longest of all of it.
First, I created a table for this self-join with the self-referential key (RELATED_TO)
This is the entire table:
Then, I wrote the query to get this result and verify my table design:
–
By getting the query working first (outside of FMP), I made sure my data relationship was what I wanted (in this case, how I interpreted your posting) and that I could get the data I wanted from it.
HTH