asked on

Sql server Getting all questions under a technology and its child (n-level)

I am having a technology table where I am having data as follows

    TechId Desc ParentId
       1    A     Null
       2    B      1
       3    C      1
       4    D      2

Open in new window

My Question table is as follows

    QId     TId      desc
     1       1        Q1
     2       2        Q2
     3       3        Q3
     4       4        Q4

Open in new window

What I am trying to get is from questions table is when I select TechId 1 it should load all questions up to nth level. Result expecting is

QId TId Desc
 1      1      Q1
  2     2       Q2
  3     3        Q3
  4     4        Q4

Open in new window

Pawan Kumar

Please try complete solution -

CREATE TABLE technology
(
	 TechId INT
	,[Desc] VARCHAR(1)
	,ParentId INT 
)
GO
    
INSERT INTO technology VALUES
(1,'A',Null),
(2,'B', 1  ),
(3,'C', 1  ),
(4,'D', 2  )
GO

CREATE TABLE Question
(
     QId  INT   
	,TId INT 
	,[desc] VARCHAR(10)
)
GO

INSERT INTO Question VALUES
(1, 1,'Q1'),
(2, 2,'Q2'),
(3, 3,'Q3'),
(4, 4,'Q4')
GO

DECLARE @techID AS INT 
;WITH CTE AS
(
	SELECT Q.*,ParentID FROM technology t 
	INNER JOIN Question Q ON Q.TId = t.TechId
)
,CTE1 AS
(
	SELECT * FROM CTE WHERE TId = @techID
)
,CTE2 AS 
(
	SELECT a.QId,a.TId,a.[desc] FROM CTE a
	UNION ALL  
	SELECT t.QId,t.TId,t.[desc]
	FROM CTE2 c INNER JOIN CTE t ON c.TId = t.ParentID
)
SELECT DISTINCT * FROM CTE2

/*------------------------
OUTPUT
------------------------*/
QId         TId         desc
----------- ----------- ----------
1           1           Q1
2           2           Q2
3           3           Q3
4           4           Q4

(4 row(s) affected)

Open in new window

OUTPUT

/*------------------------
OUTPUT
------------------------*/
QId         TId         desc
----------- ----------- ----------
1           1           Q1
2           2           Q2
3           3           Q3
4           4           Q4

(4 row(s) affected)

Open in new window

Chaitanya V

ASKER

Hi one small help can I get the count like for TechnologyId 1 it should show as 4 and for 2 as 1 respectively

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pawan Kumar

Provided verified solution.