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

Chaitanya V
Chaitanya V used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
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

Author

Commented:
Hi one small help can I get the count like for TechnologyId 1 it should show as 4 and for 2 as 1 respectively
Database Expert
Awarded 2016
Top Expert 2016
Commented:
Updated code for you -

DECLARE @techID AS INT = 1
;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 CTE1 a
	UNION ALL  
	SELECT t.QId,t.TId,t.[desc]
	FROM CTE2 c INNER JOIN CTE t ON c.TId = t.ParentID
)
SELECT DISTINCT * , COUNT(*) OVER() Cnt FROM CTE2

Open in new window


Output

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

(4 row(s) affected)

Open in new window

Pawan KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
Provided verified solution.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial