[SOLVED] How to map child parent on a single table using self Join in SQL Server?

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

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

Solution

You are doing it backward.

  FROM [dbo].[ChildParent] P
  LEFT JOIN [dbo].[ChildParent] C ON P.ParentID = C.ID