Link to home
Create AccountLog in
Avatar of Chaitanya V
Chaitanya V

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

Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

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

Avatar of Chaitanya V
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
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Provided verified solution.