• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 555
  • Last Modified:

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
0
lrbrister
Asked:
lrbrister
  • 2
1 Solution
 
Scott PletcherSenior DBACommented:
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
0
 
sarabhaiCommented:
GO
WITH ln(id, parentID, pText, levelno) AS
(
    SELECT id, parentID, pText, 0 AS levelno
    FROM tableA
    WHERE parentID IS NULL
    UNION ALL
    SELECT e.id, e.parentID, e.pText, levelno + 1
    FROM tableA AS e
        INNER JOIN ln AS d
        ON e.parentID = d.id
)
SELECT id, parentID, pText, levelno
FROM ln
WHERE levelno <= 3 ;
GO

you can set the values of WHERE parentID IS NULL
and WHERE levelno <= 3 ;
as you needed.
0
 
sarabhaiCommented:
here you can set under which parentID  WHERE parentID IS NULL
and upto how many levels WHERE levelno <= 3 ;
0
 
lrbristerAuthor Commented:
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
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now