CODEWITHSUNDEEP
sqlsql-serversql-server-2008
riyas nawab
riyas nawab

Reputation: 101

Select query to reference tables based on table Id or Name

Consider I have a table which list all the tables in database along with the referenced tables. In my case, no foreign key reference are used in the tables. Tables references are maintained as below 

                     TableId   ReferedInTableId
                        1         2
                        1         3
                        1         4
                        2         5
                        2         6
                        3         7
                        4         8
                        4         9
                        5         -
                        6         -
                        7         10
                        8         -
                        9         11
                        10        -
                        11        -

In this case, I need a query to find the referenced tables based on the input TableId.

For Eg, for TableId 1, the referencedTableId are 2,3,4. But I need to recurse again like these 2,3,4 are again referred in some tables and I need that list too.

At the end,

If the input is TableId 1, It should return 2,3,4,5,6,7,8,9,10,11

If the input is tableId 4, It should return 8,9,11

If the input is tableId 3, it should return 7,10

Please help me in building a SQL select query.

Upvotes: 1

Views: 1883

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

Assuming you have no cycles in the links, you can use a relatively simple recursive CTE:

with cte as (
      select t.tableid, t.referencedtableid, 1 as lev
      from t
      where t.id = 1
      union all
      select cte.tableid, t.referencedtableid, lev + 1
      from cte join
           t
           on cte.referencedtableid = t.id
    )
select referencedtableid
from cte;

Upvotes: 1

Related Questions