Recursively fetching parent ID if reference ID IS NULL

I am not sure if I should do this in the code or do it in the query, but I will ask it here as I am interested in a sql solution for myself.

Say if I have the following table, and I need to get all the rows whose ParentSID is 1. But if any of these rows return have Null as FID then I also need to go fetch all rows whose ParentSID is the SID of the row whose FID was Null.

There is no limitation and it could go on and on where the records return could have Null as FIDs and I have to find all rows with its ParentSID.

----------------------
SID    FID    ParentSID

1      null    null          
2      null    null
3      16      1
4      17      1
5      null    1
6      18      5
7      19      2   
----------------------

I have iterative solution in code but I have to go back multiple times to the database to get the co开发者_如何转开发mplete list I am interested in and I am sure having a SQL procedure would make much more sense, but I am not sure how to do it?

Thanks,

Voodoo

I had to use two recursive CTEs, one on top of the other, to get the output I believe you expect:

WITH hierarchy AS (
    SELECT *
      FROM dbo.[Table]
     WHERE parentsid = 1
    UNION ALL
    SELECT t.*
      FROM hierarchy h
      JOIN dbo.[Table] t ON h.sid = t.parentsid
                        AND t.fid IS NULL),
     h2 AS (
    SELECT h.*
      FROM hierarchy h
    UNION ALL
    SELECT t.*
      FROM hierarchy h
      JOIN dbo.[Table] t ON h.sid = t.parentsid
                        AND t.fid IS NOT NULL)      
 SELECT *
   FROM h2

The first one gets all those that are NULL; the second gets the the doglegs where the fid is not null.

Microsoft has made this much much easier - I believe since 2005. CTEs (common table expressions) and recursion. I could repeat how but MS has a good example...

http://msdn.microsoft.com/en-us/library/ms186243.aspx

This is kind of a classical problem I suppose. You might wish to take a look here: Recursion in T-SQL. In my humble opinion, however, it might be worthwhile considering if you can flatten your data structure somewhere else (i.e. a cache in another table) and then query that more easily. Up to you. Searching around for various 'Recursive T-SQL' should lead you to some interesting options, if you decide to do the looping there. FWIW, when I've encountered this, I've just done it in code (but my context may be different to yours).

The following Query give you what is expected. select * from @table A where ParentSID=1 OR ParentSID IN (select SID from @table A where ParentSID=1 AND FID is NULL)

I have illustrated this in an example

Example

declare @table table ([SID] int, FID int, ParentSID int)

insert into @table Values(1, null, null)
insert into @table Values(2, null, null)
insert into @table Values(3, 16, 1)
insert into @table Values(4, 17, 1)
insert into @table Values(5, null, 1)
insert into @table Values(6, 18, 5)
insert into @table Values(7, null, 1)
insert into @table Values(8, 19, 7)

select * from 
    @table A
where ParentSID=1 OR
ParentSID IN (select SID from 
    @table A
where ParentSID=1 AND FID is NULL)

This is very simple :

if(not exists(select  * 
              from    table 
              where   FID is null
                      and
                      ParentSID = 1)  )

    select 
        * 
    from 
        table
    where 
        ParentSID = 1
else
    select 
        * 
    from 
        table
    where
        ParentSID = SID