SQL experts, CTE's?

[Mattius]

I have a table of connections which is stored, so i know component a connects to b etc.

what i have to do is give in a parameter so component f and find out every connection from that, that has no children so bassically the end node from that connection, there could be many.

Bare in mind this is a database with over 120,000 connections.

I did try using a cursor and a stored procedure but we soon reached the limit of 32 levels in sql.

So i believe cte's are what we would need, anyone any idea? cause its got everyone in the office stumped.

 

[philldutton]

You should be able to use a recursive CTE, without examples of data and the db schema it's a bit tricky to offer specific help.

 

[Mattius]

I'll try and provide you with as much info as i can:

Database has one table we are needing to use, and basically two id's we can use

incID - incoming connection component id
outID - outgoing connection component id

we need to find all components from a connection which dont have an outid

it is a basic tree structure we are working through,

so basically we start with 7293 and find out its children, then we need to find the children or them and so on, allong the way if any object doesnt have children i need to store that and show them all at the end.

I just dont know where to even start with a recursive CTE

 

[Mattius]

I know what i want to do in normal programming language but t-sql is confusing me, what i want to do is

select all outgoing connections from object
   for each outgoing connection
      if connection has outgoings
         run again for the outgoing
      else
        store id

return results

 

[dasyad]

I'd write a little application to do it to be honest you could even write an SSIS package to do it I think.

 

[Mattius]

for anyone interested in stupidly complicated sql, we got it running, unfortunatly we also discovered there is loops in the database which causes the recursion limit of 100 to be met.

Until the client checks the 120,000 connections we arent gonna be able to achieve it i dont think, ah the joys of creating a structured database from excell spreadsheets!

here is the code we got working which unfortunatly hits the loops

Declare @termid as int

Declare @nodeid as int

 

DECLARE nodeCursor CURSOR FAST_FORWARD FOR 

WITH org_name AS

(SELECT     c1.incID AS parent_id,

            n1.description AS parent_name,

            c1.outID AS child_id,

            n2.description AS child_name

FROM components n1 RIGHT JOIN connections c1

ON         n1.ID = c1.incID RIGHT JOIN components n2

ON         n2.ID = c1.outID)

, jn AS

(SELECT    parent_id, parent_name,

            child_id, child_name

  FROM      org_name

  WHERE     parent_id = 76394

  UNION ALL

  SELECT    c.parent_id, c.parent_name,

            c.child_id, c.child_name

  FROM      jn AS p JOIN org_name AS c

  ON c.parent_id = p.child_id)

 

SELECT  j1.child_id AS termNode

FROM  jn AS j1 LEFT JOIN jn AS j2

ON          j1.child_id = j2.parent_id

WHERE j2.child_id is null

     

OPEN nodeCursor

FETCH NEXT FROM nodeCursor

INTO @termid

 

WHILE @@FETCH_STATUS = 0

BEGIN

print 'Terminal ID: ' + CAST(@termid AS varchar(5))

            DECLARE nodeCursorUp CURSOR FAST_FORWARD FOR 

            WITH org_name AS

            (SELECT     c1.incID AS parent_id,

                        n1.description AS parent_name,

                        c1.outID AS child_id,

                        n2.description AS child_name

            FROM components n1 RIGHT JOIN connections c1

            ON         n1.ID = c1.incID RIGHT JOIN components n2

            ON         n2.ID = c1.outID)

            , jn AS

            (SELECT    parent_id, parent_name,

                        child_id, child_name

              FROM      org_name

              WHERE     child_id = @termid

              UNION ALL

              SELECT    c.parent_id, c.parent_name,

                        c.child_id, c.child_name

              FROM      jn AS p JOIN org_name AS c

              ON c.child_id = p.parent_id)

 

            SELECT  parent_id

            FROM  jn

            order by 1 desc

 

            OPEN nodeCursorUp

            FETCH NEXT FROM nodeCursorUp

            INTO @nodeid

 

            WHILE @@FETCH_STATUS = 0

            BEGIN

            print 'Node ID to surface: ' + CAST(@nodeid AS varchar(5))

 

            FETCH NEXT FROM nodeCursorUp

            INTO @nodeid

            END

 

            CLOSE nodeCursorUp

            DEALLOCATE nodeCursorUp

           

FETCH NEXT FROM nodeCursor

INTO @termid

END

 

CLOSE nodeCursor

DEALLOCATE nodeCursor

 

[AshenShugar1873]

I am assuming your table is called Connections and the parent field is Master, the child field is Detail. This allows you to filter on the Parent and shouldn't hit the connection limit. Also shows the nesting level. Give it a try and let me know if it works.

with t2 as (
select master, detail, 0 as level
from Connections as t1
where master = 1

union all

select t1.master, t1.detail, level + 1
from connections t1
join t2 on t2.detail = t1.master
)

select * from t2


EDIT: Reading again, I take it you mean the database has children that relate back to earlier parents? That is a problem

 

[Mattius]

Yeah there is some loops we discovered some 10 levels down, that stops any recursive procedure in its tracks.

 

[Goksly]

With cte you can get recursive limit / depth.

 

[Mattius]

yeah but that would stop it in all cases, we only want to stop it when it finds a loop.