How to get result from parent child table

Question

Work on SQL-Server. My table structure is below

CREATE TABLE [dbo].[AgentInfo](
    [AgentID] [int] NOT NULL,
    [ParentID] [int] NULL,
 CONSTRAINT [PK_AgentInfo] PRIMARY KEY CLUSTERED 
(
    [AgentID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[AgentInfo] ([AgentID], [ParentID]) VALUES (1, -1)
INSERT [dbo].[AgentInfo] ([AgentID], [ParentID]) VALUES (2, -1)
INSERT [dbo].[AgentInfo] ([AgentID], [ParentID]) VALUES (3, 1)
INSERT [dbo].[AgentInfo] ([AgentID], [ParentID]) VALUES (4, 2)

Required output

Use my below syntax get required output but not satisfied. Is there any better way to get the required output

enter image description here

--get parent child list
---step--1 
SELECT * 
INTO #temp1 
FROM  ( SELECT a.AgentID ,
            a.ParentID,
            a.AgentID AS BaseAgent
        FROM dbo.AgentInfo a WHERE ParentID=-1
        UNION ALL         
        SELECT   a.ParentID  ,
            0 as AgentID,
            a.AgentID AS BaseAgent 
        FROM dbo.AgentInfo a WHERE ParentID!=-1
        UNION ALL
        SELECT   a.AgentID  ,
            a.ParentID,
            a.AgentID AS BaseAgent 
        FROM dbo.AgentInfo a 
        WHERE ParentID!=-1 
     ) AS d

SELECT * FROM #temp1
DROP TABLE #temp1

Help me to improve my syntax. If you have any questions please ask.

I think it's for multi root tree selecting... I would suggest using nested sets through. They are easier to select and faster to select. If your tree does change often it's not as good through. — Hikaru-Shindo, Jan 18 '13 at 08:19
I don't understand that if agent 3 and 4 has a parent as 1 and 2, then why agent 1 and 2 has the parent as 3 and 4 too. A child can't be the parent of its parent. Something is very confusing here. — András Ottó, Jan 18 '13 at 08:36
This correction makes even less sense... The BaseAgent column is now totally off. Your BaseAgents are 1 and 2, right? — Jacco, Jan 18 '13 at 09:04
You should explain what does BaseAgent, AgentID and ParentID means, because I can't understand you result table, and a recursion is not possible to achive this kind of data, because it would be an endless loop. — András Ottó, Jan 18 '13 at 09:46

score 0 · Accepted Answer · answered Jan 18 '13 at 08:24

You could use a recursive SELECT, see the examples in the documentation for WITH, starting with example D.

The general idea within the recursive WITH is: You have a first select that is the starting point, and then a UNION ALL and a second SELECT which describes the step from on level to the next, where the previous level can either be the result of the first select or the result of the previous run of the second SELECT.

score 0 · Answer 2 · answered Jan 18 '13 at 08:39

You can try this, to get a tree of the elements:

WITH CTE_AgentInfo(AgentID, ParentID, BaseAgent)
AS(
  SELECT 
    AgentID,
    ParentID,
    AgentID AS BaseAgent
   FROM AgentInfo
   WHERE ParentID = -1
  UNION ALL
     SELECT 
    a.AgentID,
    a.ParentID,
    a.AgentID AS BaseAgent
   FROM AgentInfo a
   INNER JOIN CTE_AgentInfo c ON
    c.AgentID = a.ParentID
  )
SELECT * FROM CTE_AgentInfo

And here is an SQLFiddle demo to see it.

score 0 · Answer 3 · answered Jan 18 '13 at 08:40

Try something like this:

WITH Merged (AgentId, ParentId) AS (
     SELECT AgentId, ParentId FROM AgentInfo WHERE ParentId = -1
     UNION ALL
     SELECT AgentInfo.AgentId, AgentInfo.ParentId FROM AgentInfo INNER JOIN Merged ON AgentInfo.AgentId = Merged.ParentId
)
SELECT * FROM Merged

score 0 · Answer 4 · edited May 23 '17 at 11:56

You can use a Common Table Expression to do this.

The sql statement will then look like this:

WITH [Parents]([AgentID], [ParentID], [BaseAgent])
AS
(
    SELECT 
      [AgentID],
      [ParentID],
      [AgentID] AS [BaseAgent]
    FROM [AgentInfo]
    WHERE [ParentID] = -1
    UNION ALL
    SELECT 
      [ai].[AgentID],
      [ai].[ParentID],
      [p].[BaseAgent]
    FROM [AgentInfo] [ai]
    INNER JOIN [Parents] [p] 
      ON [ai].[ParentID] = [p].[AgentID]
)

SELECT *
FROM [Parents]
ORDER BY 
  [BaseAgent] ASC,
  [AgentID] ASC

But, the results are different from your desired output, since every Agent is only listed once. The output is:

AGENTID     PARENTID    BASEAGENT
1           -1          1
3            1          1
2           -1          2
4            2          2

The Fiddle is over here.

And here is a nice post on working with hierarchies: What are the options for storing hierarchical data in a relational database?

How to get result from parent child table

4 Answers4

Linked

Related