Geek Answers Handbook

Help with SQL query?

I'm not sure if I should do this in code or do this in a query, but I will ask for it here, as I am interested in solving sql 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 returns Null as FID , then I also need to fetch all the rows, ParentSID is the SID of the row whose FID was Null .

There are no restrictions, and it can go on and on where the return of the records can be Null as FIDs , and I need to find all the rows with 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 an iterative solution in the code, but I need to return several times to the database to get the complete list that interests me, and I'm sure the SQL procedure will make more sense, but I'm not sure how to do this?

Thanks,

voodoo

+4
source share
5 answers

I had to use two recursive CTEs, one on top of the other, to get the result that I expect you to 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 gets all those that are NULL; the second gets doglegs where the fid not null.

+2
source

Microsoft has made it a lot easier - I believe that since 2005. CTE (common table expressions) and recursion. I could repeat, but MS has a good example ...

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

+2
source

This is a kind of classic problem, I suppose. You can look here: Recursion in T-SQL . However, in my humble opinion, it is worth considering whether it is possible to smooth the data structure elsewhere (i.e., the cache in another table), and then request it more easily. You decide. A search for various "recursive T-SQLs" should lead you to some interesting parameters if you decide to make a loop. FWIW, when I came across this, I just did it in code (but my context may be different from yours).

+1
source

The following query will 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 - NULL)

I 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)
+1
source

It 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
0
source

More articles:


All Articles