CODEWITHSUNDEEP
sqlsql-servert-sqlhierarchical-datarecursive-query
Debbie.S
Debbie.S

Reputation: 167

How can i do a recursive select from the same Table in SQL?

I have a table with the following

Table1:

ObjectName  Field           Type      Link

Account     Name            String          
Asset       AccountId       Guid      Account       
Asset       Name            String          
Account     BillingStreet   String    Details       
Details     FirstName       String    Order
Order       OrderNo         Integer  
Order       OrderItem       String

The primary point of reference is Asset. what i mean by that is that I can do a select * from Table1 where ObjectName = 'Asset'. That would give me 2 rows. But there is a Link to Asset ie Account . So if there is a link on the select above then select all rows with that name

So something like:

select * from Asset , if there is a value in Link column then select all rows that have an ObjectName of the Link Name and keep going until there is no more. So it would finish when it selects the Order
because there is no Link on Order

If i was to do a select * from Table1 where ObjectName = 'Account', then since there is a Link in on of the rows , I would need to select Details row and in turn details has a a link to Order and I need to select Order and since Order has no Linhk , thats it

How can i do this complex query in sql, dont know where to start from ?

How can i do this complex query in sql ?

Upvotes: 0

Views: 282

Answers (1)

GMB
GMB

Reputation: 222462

You are describing a hierarchical query. The syntax slightly varies across databases (and not all databases implement this feature), but the idea is:

with recursive cte (objectname, field, type, link) as (
    select t.* from table1 t where objectname = 'Asset'
    union all
    select t.*
    from table1 t
    inner join cte c on c.link = t.objectname
)
select * from cte

Upvotes: 3

Related Questions