search through a hierarchy with SQL

1683 views sql
1

I've got objects with an hierarchical order. Each object has an ID, a parent object (TO_ID in the table) and a type. My data is in a table which looks like this:

ID  | TO_ID | TYPE
123 | 103   | group
176 | 103   | field
256 | 169   | group
103 | 234   | organization

Now I want to search through the table until I find a parent object with a specific type (I don't know hot many parent object my start object has). How can I solve this with SQL?

answered question

Could you show the required output based on your data?

1 Answer

11

You need to use Self Join for that purpose.

Select * from Table1 TB1 join Table1 TB2 on TB1.ID = TB2.TO_ID where TB1.type = group

Hope this will helps you.

posted this

Have an answer?

JD

Please login first before posting an answer.