seo Question posted in
Some basic information to get you started with this can be found here.

How to map child parent on a single table using self Join in SQL Server? – SEO

I have the following table with child parent relationship.

ID      Title               PageID  IsParent    ParentID    IsActive
1       Dashboard           2125    True        NULL        True
2       Site Analytics      22      False       NULL        True
3       SEO Management      1       NULL        NULL        True
4       Mail Management     32      NULL        NULL        True
5       Build Mobile App    3214    NULL        NULL        True
6       Market Analytics    1321    NULL        NULL        True
7       Customize           235345  NULL        NULL        True
8       Reporter            253     NULL        NULL        True
9       Editor              545     NULL        NULL        True
10      News Template       45      NULL        NULL        True
11      Test Menu           0       True        3           True
NULL    NULL                NULL    NULL        NULL        NULL

Here ParentID defines the relationship between parent and child. For example, in the above table Test Menu is the child of Site Analytics. And I have the following SQL Query.

SELECT
        P.ID
        ,P.Title AS Parent
        ,C.Title AS Child
        ,P.PageID
        ,P.IsParent
        ,P.ParentID
        ,P.IsActive
      FROM [dbo].[ChildParent] P
      LEFT JOIN [dbo].[ChildParent] C ON P.ID = C.ParentID

Which results following output. 

1   Dashboard           NULL            2125    1       NULL    1
2   Site Analytics      NULL            22      0       NULL    1
3   SEO Management      Test Menu       1       NULL    NULL    1
4   Mail Management     NULL            32      NULL    NULL    1
5   Build Mobile App    NULL            3214    NULL    NULL    1
6   Market Analytics    NULL            1321    NULL    NULL    1
7   Customize           NULL            235345  NULL    NULL    1
8   Reporter            NULL            253     NULL    NULL    1
9   Editor              NULL            545     NULL    NULL    1
10  News Template       NULL            45      NULL    NULL    1
11  Test Menu           NULL            0       1       3       1

Basically, what I want to achieve is :

1   Dashboard           NULL            2125    1       NULL    1
2   Site Analytics      NULL            22      0       NULL    1
3   SEO Management      NULL            1       NULL    NULL    1
4   Mail Management     NULL            32      NULL    NULL    1
5   Build Mobile App    NULL            3214    NULL    NULL    1
6   Market Analytics    NULL            1321    NULL    NULL    1
7   Customize           NULL            235345  NULL    NULL    1
8   Reporter            NULL            253     NULL    NULL    1
9   Editor              NULL            545     NULL    NULL    1
10  News Template       NULL            45      NULL    NULL    1
11  Test Menu           SEO Management  0       1       3       1
Tags:

2

Answers


  1. 0

    Try this little change in your query:

    SELECT
        P.ID
        ,P.Title AS Parent
        ,C.Title AS Child
        ,P.PageID
        ,P.IsParent
        ,P.ParentID
        ,P.IsActive
      FROM [dbo].[ChildParent] P
      LEFT JOIN [dbo].[ChildParent] C ON isnull(P.ParentID, P.ID)  = c.id and C.ParentID is not null
    Login or Signup to reply.
  2. 0

    You are doing it backward.

      FROM [dbo].[ChildParent] P
  LEFT JOIN [dbo].[ChildParent] C ON P.ParentID = C.ID
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search