Recursion On A Many To Many Table Parent To Child To Parent

Question

My boss has given me a single table.

Related_Items_Table

Item        | Accessory 
---------------------
TV          | Antennae 
TV          | Power Cord 
TV          | Remote 
Laptop      | Power Cord 
Laptop      | Carrying Case 
Camera      | Carrying Case 
Camera      | Lens 
iPod        | Headphones

The best way to describe what my boss wants for results is to walk through the process.

1. The user searches for TV.

2. TV is found and the accessories for TV are Antennae, Power Cord & Remote.

3. The accessories Antennae, Power Cord & Remote are now used to find other related items. Power Cord also an accessory for Laptop. Antennae & Remote are not accessories for any other item.

4. The item Laptop is now used to find that item's accessories, which are Power Cord & Carrying Case.

5. The accessories Power Cord & Carrying Case are now used to find other related items. Power Cord finds no new items (we already know Power Cord is associated with TV & Laptop). Carrying Case is also an accessory for Camera.

6. The item Camera is now used to find that item's accessories, which are Carrying Case & Lens.

7. The accessories Carrying Case & Lens are now used to find other related items. Carrying Case & Lens find no new items (we already know Carrying Case is associated with Laptop).

8. No new items are found to continue search chain. Final list returned.

Final List 

Item        | Accessory 
---------------------
TV          | Antennae 
TV          | Power Cord 
TV          | Remote 
Laptop      | Power Cord 
Laptop      | Carrying Case 
Camera      | Carrying Case 
Camera      | Lens 

What is the best way to handle this problem? I'm not sure what the correct terminology would be for this so perhaps I missed it in my searches. Any advice is appreciated.

Answer 1

It looks like your table presents a undirected graph and you need to traverse this graph starting from the Item user searched.

Consider using breadth-first search (BFS) algorithm.

Every visited node is a resulting list you need.