Sk Borhan Uddin Sk Borhan Uddin - 7 months ago 8
SQL Question

Get data from same table with the compare of multiple rows?

I have a table of comtent like bellow...

ProjectID | ParentID | Project Name ...
----------------------------------------
1 | 1 | Project Name 1...
2 | 2 | Project Name 2...
3 | 3 | Project Name 3...
4 | 1 | Project Name 4 & Sub Project 1...
5 | 1 | Project Name 5 & Sub Project 1...
6 | 1 | Project Name 6 & Sub Project 1...
7 | 4 | Project Name 7 & Sub Project 1 & Sub Project 4...
8 | 4 | Project Name 8 & Sub Project 1 & Sub Project 4...
9 | 9 | Project Name 9...


In here ProjectID is primary key & ParentID indicates the primary or main ProjectID of sub project. If ProjectID = ParentID it means it is primary (main) project

Now the question is if I want to fetch out the sub-projects of sub-projects that means,
If I want to see the sub-projects under 1 and out put'll be ...

ProjectID | ParentID | Project Name ...
----------------------------------------
1 | 1 | Project Name 1...
4 | 1 | Project Name 4 & Sub Project 1...
5 | 1 | Project Name 5 & Sub Project 1...
6 | 1 | Project Name 6 & Sub Project 1...
7 | 4* | Project Name 7 & Sub Project 1 & Sub Project 4...
8 | 4* | Project Name 8 & Sub Project 1 & Sub Project 4...



  • here 4 is the sub project of 1 and 7,8 are the sub project of 4



How do I compare? Do I have to use #temp for 1 and then consider the primary ID of #temp table? or is there any better solution?

Answer

I found the way to solve. But the actual solution is given by Jayvee. Sorry I didnt know the method it calls, so I think I didnt explain it perfectly. Actually I want to use recursion here on aspect of ProjectID.

DECLARE @ProjectID int = 1
WITH DirectReports 
AS
(
-- Anchor member definition
    SELECT C.*
    FROM tblProject AS C
    WHERE C.ProjectID = @ProjectID
    UNION ALL
-- Recursive member definition    
    SELECT C1.*
    FROM tblProject AS C1
    JOIN DirectReports AS d ON C1.ProjectID = d.ProjectID
)
-- Statement that executes the CTE
SELECT * FROM DirectReports
UNION ALL
SELECT C.*
FROM tblProject AS C
WHERE C.ProjectID = @ProjectID and C.ProjectID=C.ParentID

More elaboration you may find Here. Thanks for contribution.