Expand Parent - Node Relationship in SQL

Hi all,

I am trying to remove the need to perform multiple SQL queries to get a long winded list of parent node relationships from a table.

I have a table that contains a list of the parent-child relationships, but would ideally like to create a view to expand this further.

For example, if I have a Tree such as:
a - b
    c - d
        e
f - d
    e

Open in new window

This could be represented in a table of links where
parent  child
null    a
null    f
a       b
a       c
c       d
c       e
f       d
f       e

Open in new window

it is possible for a child to have more than one parent as this simply a link from one point to another and not designed to be a structured tree.

What I would like to see is essentially a view containing all the nodes and ALL their children

For the example above, this would give:
Node    Child
a       null
a       b
a       c
a       d
a       e
b       null
c       null
c       d
c       e
d       null
e       null
f       null
f       d
f       e

Open in new window


With this, I can simply select where Node is 'a' and know that b, c, d and e are children (not necessarily direct)
I have included a null entry for the node itself so that there is always at least one entry for every node.
This is not necessary, or could even be the node itself (i.e. f - null could be f - f)

Any ideas on how I can achieve this?

Many thanks,

James
LVL 4
James AtkinSenior Principle Software EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BeartlaoiCommented:
This will require multiple queries, a view cannot do this on its own.
A stored procedure however can do this since it can contain looping logic.
If you want to pursue this, please provide your version of SQL and I can create a prototype for you.
0
James AtkinSenior Principle Software EngineerAuthor Commented:
Hi,
Thanks for the response.
I believed that this would be the case, but was hoping not ;-)
A prototype would be very helpful though.
I am running SQL Server 2012.
Thanks a lot,
James
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You can do it in one statement by using a common table expression (CTE):
;WITH CTE_NoParent (Node, Child)
AS
	(SELECT CASE 
				WHEN C.Parent IS NULL THEN P.Child
				ELSE C.Parent
			END,
			CASE 
				WHEN C.Parent IS NULL THEN C.Parent
				ELSE P.Child
			END
	FROM YourTableNameHere P
		INNER JOIN YourTableNameHere C ON P.Parent=C.Child)
SELECT Node, Child
FROM CTE_NoParent
UNION ALL
SELECT 
	CASE 
		WHEN Parent IS NULL THEN Child
		ELSE Parent
	END,
	CASE 
		WHEN Parent IS NULL THEN Parent
		ELSE Child
	END
FROM YourTableNameHere
ORDER BY 1, 2

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

BeartlaoiCommented:
There are two nested loops in this, the first essentially runs through all the parent nodes, and allows you to filter them.
The inside loop keeps adding children of children, it stops when no more are found.
--CREATE TABLE Links(Parent varchar(20), Child varchar(20))
ALTER Procedure GetChildren
AS
CREATE TABLE #OutLinks(Parent varchar(20), Child varchar(20))
CREATE TABLE #OutLinks2(Parent varchar(20), Child varchar(20))
DECLARE @@Child varchar(20)
DECLARE Links_cursor CURSOR FOR SELECT Child FROM Links /* WHERE ... */ GROUP BY Child
OPEN Links_cursor
FETCH NEXT FROM Links_cursor INTO @@Child
WHILE @@FETCH_STATUS = 0
BEGIN
	INSERT INTO #OutLinks VALUES(@@Child, NULL)
	INSERT INTO #OutLinks SELECT @@Child, Child FROM Links WHERE Parent=@@Child
	WHILE 1=1
	BEGIN
		INSERT INTO #OutLinks2 SELECT Parent=@@Child, Child FROM Links WHERE Parent IN (SELECT Child FROM #OutLinks WHERE #OutLinks.Parent=@@Child)
		INSERT INTO #OutLinks SELECT Parent, Child FROM #OutLinks2 WHERE NOT EXISTS(SELECT Child FROM #OutLinks WHERE #OutLinks.Parent=#OutLinks2.Parent AND #OutLinks.Child=#OutLinks2.Child)
		TRUNCATE TABLE #OutLinks2
		IF (@@ROWCOUNT = 0) BREAK
	END
	FETCH NEXT FROM Links_cursor INTO @@Child
END
CLOSE Links_cursor
DEALLOCATE Links_cursor
SELECT * FROM #OutLinks
DROP TABLE #OutLinks
GO

Open in new window

0
James AtkinSenior Principle Software EngineerAuthor Commented:
Two excellent solutions!  Hope it is okay to split the points?
Thank you both so much for the help...
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Personally I would avoid the user of cursors since they are performance killers.
Also I can't see the purpose of using temporary tables to return data if you can do it with SELECTs only.

In small tables you can't see the difference but if we are talking about thousands of records you will see the impact in the performance.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.