Let's say I have one table which specifies an item's ID and type, and a second table which contains parent/child associations from the first table. E.g.:
`items`
id | type
---+-----
1 | 1
2 | 1
3 | 1
4 | 2
5 | 2
`associations`
parent_id | child_id
----------+---------
3 | 1
3 | 2
5 | 3
5 | 4
As you can see, there are multiple levels of inheritance possible. What I am aiming to do is write a query which would select every level of inheritance from, say, #5 in items, which would return something along the lines of
child_id | child_type
---------+-----------
3 | 1
4 | 2
1 | 1
2 | 1
Only items of type 2 can have children, which can be type 1 items or other type 2's. What I'm wanting to know if there's a simple method to recursively select all child, grandchild etc. elements of a particular item. Any ideas?
