T-SQL stored procedure stuck on getting rows that are in another col

Question

Having brain failure today, trying to get all the rows that have the value that equal another column. Problem I'm running into, is that an item can be re-inserted infinite times referring to a previous entry. I'm trying desperately not to loop. The table is set up with a primary id.

A row is created with a primary id, we'll call this row A. Another row can then be inserted that references the primary id in row A, and then the primary id is entered into the refid col, we'll call this row B. Then another row can be created either referring to row A or row B, depending on origin of it's insert call (row C). And so on.

Row A: primaryid: 1, refid: null
Row B: primaryid: 2, refid: 1
Row C: primaryid: 3, refid: 2
Row D: primaryid: 4, refid: 1
Row E: primaryid: 5, refid: 4

In my stored procedure, I am passing in the original primaryid (1). I need to return all rows that are row related to row A, which would include all the rows.

And that's where I keep getting stuck. Anyone have any suggestions for a query? We're using SQL Server 2008.

Thanks.

here's one of many duplicates: http://stackoverflow.com/questions/13857329/how-to-get-all-children-of-a-parent-and-then-their-children-using-recursion-in-q — Tab Alleman, Sep 29 '15 at 19:18
thank you guys. brain fart day, couldn't even think of recursion as the word I needed. — Mark S, Sep 29 '15 at 19:32

score 0 · Answer 1 · answered Sep 29 '15 at 19:39

This example should help you with your task:

DECLARE @table TABLE (primaryid int, refid int)

INSERT INTO @table ( primaryid, refid )
VALUES  ( 1   ,  NULL)
INSERT INTO @table ( primaryid, refid )
VALUES  ( 2   ,  1)
INSERT INTO @table ( primaryid, refid )
VALUES  ( 3   ,  2)
INSERT INTO @table ( primaryid, refid )
VALUES  ( 4   ,  1)
INSERT INTO @table ( primaryid, refid )
VALUES  ( 5   ,  4) ;
INSERT INTO @table ( primaryid, refid )
VALUES  ( 6   ,  null)  ;
INSERT INTO @table ( primaryid, refid )
VALUES  ( 7   ,  6) ;
INSERT INTO @table ( primaryid, refid )
VALUES  ( 8   ,  4) ;
INSERT INTO @table ( primaryid, refid )
VALUES  ( 9   ,  7) ;


WITH  Vals( primaryId, refid )
AS
(
    SELECT t.primaryid, t.refid
    FROM
        @Table t
    WHERE
        t.primaryid = 1
UNION ALL
SELECT t.primaryid, t.refid
FROM
    @Table t
    INNER JOIN
        Vals v
    ON
        t.refid = v.primaryId
)
SELECT * FROM vals

T-SQL stored procedure stuck on getting rows that are in another col

1 Answers1