title: How to map (Auto Mapping) in parent child relation in same table SQL server
body: <p>I have a SQL table in database with the data</p>
  
  <p>But now requirement has been changed and I'm introducing a new column <code>ParentID</code> in the table</p>
  
  <blockquote>
    <p>Table structure</p>
  </blockquote>
  
  <pre><code>CREATE TABLE [dbo].[EopsModule](
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [ModuleCode] [nvarchar](50) NOT NULL,
      [Description] [nvarchar](200) NOT NULL,
      [IsDefaultModule] [bit] NOT NULL
   CONSTRAINT [PK_EopsModule] PRIMARY KEY CLUSTERED 
  (
      [ID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  </code></pre>
  
  <blockquote>
    <p>Here is the output of the table</p>
  </blockquote>
  
  <pre><code>SELECT * FROM EopsModule
  
  //Output
  
  ID  ModuleCode  Description IsDefaultModule
  1   EW001.00    Webshop standaard   1
  2   EW001.01    Algemene functionaliteit en configuratie    1
  3   EW001.01.1  Single- of Multishop    1
  4   EW001.01.1.1    Optioneel: Multishop    1
  5   EW001.01.2  Taal    1
  6   EW001.01.2.1    Optioneel: Multi taal   1
  7   EW001.02    Design &amp; Lay-out    1
  8   EW001.02.1  Standaard Lay-out wireframe / elementen 1
  9   EW001.02.2  Responsive / Mobile Friendly    1
  10  EW001.03    Content 1
  11  EW001.03.1  Optioneel: Pagina artikelen (Website module)    1
  12  EW001.03.1.1    Optioneel: Content header &amp; footer (Website module) 1
  13  EW001.03.2  Pagina Teksten  1
  14  EW001.03.3  Optioneel: Slider (Website module)  1
  15  EW001.03.4  Contact pagina  1
  16  EW001.03.5  Optioneel: Bel me terug 1
  17  EW001.03.6  Optioneel: FAQ (Website module) 1
  18  EW001.03.7  Optioneel: Nieuws (Website module)  1
  19  EW001.03.8  Navigatie menu  1
  20  EW001.03.8.1    Assortiment navigatie uitklapmenu   1
  21  EW001.03.9  Pagina Tracking Scripts 1
  22  EW001.03.10 URL Re-directs  1
  23  EW001.03.11 Cookie Notificatie / Privacy Policy 1
  24  EW001.03.12 Error pagina’s  1
  25  EW001.04    Login &amp; Registratie 1
  26  EW001.04.1  Inloggen    1
  27  EW001.04.2  Inloggen met e-mailadres    1
  28  EW001.04.3  Wachtwoord vergeten 1
  29  EW001.04.4  Registreren 1
  30  EW001.04.4.1    Goedkeuringsprincipe    1
  31  EW001.04.4.2    Geen registratie    1
  32  EW001.04.4.3    Open registratie    1
  33  EW001.04.4.4    Optioneel: Gast-account 1
  34  EW001.04.4.5    Registratieformulier    1
  35  EW001.04.4.6    Registratie met ERP Data koppeling  1
  36  EW001.05    Assortiment 1
  37  EW001.05.1  Structuur   1
  38  EW001.05.2  Product detail  1
  39  EW001.05.3  Optioneel: Product relaties 1
  40  EW001.05.4  Assortiment content (extra informatie)  1
  41  EW001.05.5  Optioneel: Top Items    1
  42  EW001.05.6  Optioneel: Recent bekeken producten 1
  43  EW001.06    Zoeken  1
  44  EW001.06.1  Standaard zoeken    1
  45  EW001.06.2  Normaliseren    1
  46  EW001.07    Bestellen   1
  47  EW001.07.1  Winkelwagen 1
  48  EW001.07.1.1    Optioneel: Snel bestellen   1
  49  EW001.07.1.2    Optioneel: Orderregel opmerking 1
  50  EW001.07.1.3    Optioneel: Winkelwagen beheer   1
  51  EW001.07.2  Gegevens    1
  52  EW001.07.3  Betaling    1
  53  EW001.07.3.1    Op rekening 1
  54  EW001.07.4  Controleren 1
  55  EW001.07.4.1    Optioneel: Orderreferentie  1
  </code></pre>
  
  <p>After adding <code>ParentID</code> column in table, I need to map the primary <code>id</code> based on the <code>module code</code></p>
  
  <pre><code>ALTER TABLE [dbo].[EopsModule] ADD [ParentID] INT NULL
  
  SELECT *
  FROM [dbo].[EopsModule] WITH (NOLOCK)
  INNER JOIN
  (
  SELECT
  [BASE].[ModuleCode]
  ,   [BASE].[ParentCode]
  ,   [Parent].[ID] AS [ParentID]
  FROM
  (
  SELECT
  [EopsModule].[ModuleCode]
  ,   REPLACE( [EopsModule].[ModuleCode], RIGHT( [EopsModule].[ModuleCode], CHARINDEX( '.', REVERSE( [EopsModule].[ModuleCode] ), 0 )), '' ) AS [ParentCode]
  FROM [dbo].[EopsModule] WITH (NOLOCK)
  GROUP BY [EopsModule].[ModuleCode]
  ) AS [BASE]
  LEFT JOIN [dbo].[EopsModule] AS [Parent] WITH (NOLOCK)
  ON [BASE].[ParentCode] = [Parent].[ModuleCode]
  ) AS [Parent]
  ON [EopsModule].[ModuleCode] = [Parent].[ModuleCode]
  </code></pre>
  
  <blockquote>
    <p>Above query Output</p>
  </blockquote>
  
  <pre><code>ID  ModuleCode  Description IsDefaultModule ParentID    ModuleCode  ParentCode  ParentID
  167 EB001.00    Budgetshop  0   NULL    EB001.00    EB001   NULL
  168 EB002.00    Webshop Management  0   NULL    EB002.00    EB002   NULL
  169 EB003.00    Orders goedkeuren   0   NULL    EB003.00    EB003   NULL
  170 EB004.00    Single sign-on  0   NULL    EB004.00    EB004   NULL
  171 ED100.00    Dealer portaal  0   NULL    ED100.00    ED100   NULL
  172 EM001.00    Maatwerk    0   NULL    EM001.00    EM001   NULL
  1   EW001.00    Webshop standaard   1   NULL    EW001.00    EW001   NULL
  2   EW001.01    Algemene functionaliteit en configuratie    1   NULL    EW001.01    EW001   NULL
  3   EW001.01.1  Single- of Multishop    1   2   EW001.01.1  EW001.01    2
  4   EW001.01.1.1    Optioneel: Multishop    1   2   EW001.01.1.1    EW001.01    2
  5   EW001.01.2  Taal    1   2   EW001.01.2  EW001.01    2
  6   EW001.01.2.1    Optioneel: Multi taal   1   5   EW001.01.2.1    EW001.01.2  5
  7   EW001.02    Design &amp; Lay-out    1   NULL    EW001.02    EW001   NULL
  8   EW001.02.1  Standaard Lay-out wireframe / elementen 1   7   EW001.02.1  EW001.02    7
  9   EW001.02.2  Responsive / Mobile Friendly    1   7   EW001.02.2  EW001.02    7
  10  EW001.03    Content 1   NULL    EW001.03    EW001   NULL
  11  EW001.03.1  Optioneel: Pagina artikelen (Website module)    1   10  EW001.03.1  EW001.03    10
  12  EW001.03.1.1    Optioneel: Content header &amp; footer (Website module) 1   10  EW001.03.1.1    EW001.03    10
  22  EW001.03.10 URL Re-directs  1   10  EW001.03.10 EW001.03    10
  23  EW001.03.11 Cookie Notificatie / Privacy Policy 1   10  EW001.03.11 EW001.03    10
  24  EW001.03.12 Error pagina’s  1   10  EW001.03.12 EW001.03    10
  13  EW001.03.2  Pagina Teksten  1   10  EW001.03.2  EW001.03    10
  14  EW001.03.3  Optioneel: Slider (Website module)  1   10  EW001.03.3  EW001.03    10
  15  EW001.03.4  Contact pagina  1   10  EW001.03.4  EW001.03    10
  16  EW001.03.5  Optioneel: Bel me terug 1   10  EW001.03.5  EW001.03    10
  17  EW001.03.6  Optioneel: FAQ (Website module) 1   10  EW001.03.6  EW001.03    10
  18  EW001.03.7  Optioneel: Nieuws (Website module)  1   10  EW001.03.7  EW001.03    10
  19  EW001.03.8  Navigatie menu  1   10  EW001.03.8  EW001.03    10
  20  EW001.03.8.1    Assortiment navigatie uitklapmenu   1   19  EW001.03.8.1    EW001.03.8  19
  21  EW001.03.9  Pagina Tracking Scripts 1   10  EW001.03.9  EW001.03    10
  25  EW001.04    Login &amp; Registratie 1   NULL    EW001.04    EW001   NULL
  26  EW001.04.1  Inloggen    1   25  EW001.04.1  EW001.04    25
  27  EW001.04.2  Inloggen met e-mailadres    1   25  EW001.04.2  EW001.04    25
  28  EW001.04.3  Wachtwoord vergeten 1   25  EW001.04.3  EW001.04    25
  29  EW001.04.4  Registreren 1   25  EW001.04.4  EW001.04    25
  30  EW001.04.4.1    Goedkeuringsprincipe    1   29  EW001.04.4.1    EW001.04.4  29
  31  EW001.04.4.2    Geen registratie    1   29  EW001.04.4.2    EW001.04.4  29
  32  EW001.04.4.3    Open registratie    1   29  EW001.04.4.3    EW001.04.4  29
  33  EW001.04.4.4    Optioneel: Gast-account 1   25  EW001.04.4.4    EW001.04    25
  34  EW001.04.4.5    Registratieformulier    1   29  EW001.04.4.5    EW001.04.4  29
  35  EW001.04.4.6    Registratie met ERP Data koppeling  1   29  EW001.04.4.6    EW001.04.4  29
  36  EW001.05    Assortiment 1   NULL    EW001.05    EW001   NULL
  37  EW001.05.1  Structuur   1   36  EW001.05.1  EW001.05    36
  38  EW001.05.2  Product detail  1   36  EW001.05.2  EW001.05    36
  39  EW001.05.3  Optioneel: Product relaties 1   36  EW001.05.3  EW001.05    36
  40  EW001.05.4  Assortiment content (extra informatie)  1   36  EW001.05.4  EW001.05    36
  41  EW001.05.5  Optioneel: Top Items    1   36  EW001.05.5  EW001.05    36
  42  EW001.05.6  Optioneel: Recent bekeken producten 1   36  EW001.05.6  EW001.05    36
  43  EW001.06    Zoeken  1   NULL    EW001.06    EW001   NULL
  44  EW001.06.1  Standaard zoeken    1   43  EW001.06.1  EW001.06    43
  45  EW001.06.2  Normaliseren    1   43  EW001.06.2  EW001.06    43
  173 EW001.06.3  Optioneel: Zoeken op klant artikelnummer    1   43  EW001.06.3  EW001.06    43
  46  EW001.07    Bestellen   1   NULL    EW001.07    EW001   NULL
  47  EW001.07.1  Winkelwagen 1   46  EW001.07.1  EW001.07    46
  48  EW001.07.1.1    Optioneel: Snel bestellen   1   46  EW001.07.1.1    EW001.07    46
  49  EW001.07.1.2    Optioneel: Orderregel opmerking 1   47  EW001.07.1.2    EW001.07.1  47
  50  EW001.07.1.3    Optioneel: Winkelwagen beheer   1   47  EW001.07.1.3    EW001.07.1  47
  51  EW001.07.2  Gegevens    1   46  EW001.07.2  EW001.07    46
  52  EW001.07.3  Betaling    1   46  EW001.07.3  EW001.07    46
  53  EW001.07.3.1    Op rekening 1   52  EW001.07.3.1    EW001.07.3  52
  54  EW001.07.4  Controleren 1   46  EW001.07.4  EW001.07    46
  55  EW001.07.4.1    Optioneel: Orderreferentie  1   54  EW001.07.4.1    EW001.07.4  54
  </code></pre>
  
  <p>NOTE: Data has been mapped by using the above query, but i noticed there is a anomaly in the output i.e 
  for every child module there is a parent id mapped , but in the case of third or fourth level of child there is a conflict b/w the parent module code and the child module code and at last the id for parent for a child is not correct please find the screenshot for better understanding.</p>
  
  <p><a href="https://i.stack.imgur.com/4ymdi.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/4ymdi.png" alt="enter image description here"></a>  </p>