asked on

SQL Server parent and child records - Difficult

I have a table that has 3 primary columns

id, parentID, pText

An example of the data is

id parentID pText
1 NULL Home
2 1 Menu2
3 2 cMenu

I need a select so a column is added that shows a 1, 2, or 3
1 = Top level Parent
2 = Child with children of its own...so it is also a parent
3 = detail record...no children

Result with example data would be:
id parentID pText recType
1 NULL Home 1
2 1 Menu2 2
3 2 cMenu 3
4 2 c2Menu 3

Scott Pletcher

SELECT
...,
CASE WHEN parentID IS NULL THEN 1
CASE WHEN EXISTS(SELECT 1 FROM dbo.tablename tn2 WHERE tn2.parentID = tn.id) THEN 2
ELSE 3 END AS [RecType],
...
FROM dbo.tablename tn

ASKER CERTIFIED SOLUTION

Sara bhai

membership

This solution is only available to members.

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

Start Free Trial

Sara bhai

here you can set under which parentID WHERE parentID IS NULL
and upto how many levels WHERE levelno <= 3 ;

Larry Brister

ASKER

THis worked perfectly

Here's my final solution

;WITH ln(id, parentID, panelText, levelno,webPageSort, sort) AS
(
SELECT id, parentID, panelText, 0 AS levelno,webPageSort, sort
FROM activeDirPanelBar
WHERE parentID IS NULL
UNION ALL
SELECT e.id, e.parentID, e.panelText, levelno + 1 levelno,e.webPageSort, e.sort
FROM activeDirPanelBar AS e
INNER JOIN ln AS d
ON e.parentID = d.id
)
SELECT id, parentID, panelText, levelno,webPageSort, sort
FROM ln
WHERE levelno <= 3
ORDER BY webPageSort, sort