SQL Server parent and child records - Difficult

Posted on 2012-09-10
Last Modified: 2012-09-11
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 it is also a parent
3 = detail 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
Question by:lrbrister
    LVL 68

    Expert Comment

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

    Accepted Solution

    WITH ln(id, parentID, pText, levelno) AS
        SELECT id, parentID, pText, 0 AS levelno
        FROM tableA
        WHERE parentID IS NULL
        UNION ALL
        SELECT, e.parentID, e.pText, levelno + 1
        FROM tableA AS e
            INNER JOIN ln AS d
            ON e.parentID =
    SELECT id, parentID, pText, levelno
    FROM ln
    WHERE levelno <= 3 ;

    you can set the values of WHERE parentID IS NULL
    and WHERE levelno <= 3 ;
    as you needed.
    LVL 9

    Expert Comment

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

    Author Closing Comment

    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.parentID, e.panelText, levelno + 1 levelno,e.webPageSort, e.sort
        FROM activeDirPanelBar AS e
            INNER JOIN ln AS d
            ON e.parentID =
    SELECT id, parentID, panelText, levelno,webPageSort, sort
    FROM ln
    WHERE levelno <= 3
    ORDER BY webPageSort, sort

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now