Sql Server Self-Join Child to Parent Hierarchy

Problem:   Series of child-parent relationship rows where you need to find the topmost parent.

Solution: TBD!

Script to create the situation below…

USE TempDb
GO
— Create a Table
drop table party_merge
go
CREATE TABLE Party_merge(
FromPartyID INT PRIMARY KEY,
ToPartyID INT
)
GO
— Insert Sample Data
— 3 gets gets 4 children (1, 2, 8, 9) assigned to merge to it and then itself gets merged to id=10
— 2 is the new parent for 4, 5, and 7
— 11 gets 12 assigned. Only parent 12 has only one child (11)

— Note:
— 14 gets parent assigned as 14 (due to source system issue)

INSERT INTO Party_merge
SELECT 1, 3
UNION ALL
SELECT 2, 3
UNION ALL
SELECT 14, 14
UNION ALL
SELECT 4, 2
UNION ALL
SELECT 5, 2
UNION ALL
SELECT 7, 2
UNION ALL
SELECT 8, 3
UNION ALL
SELECT 9, 3
UNION ALL
SELECT 3, 10
UNION ALL
SELECT 11, 12
GO
— Check the data

SELECT *
FROM Party_merge
ORDER BY FromPartyID, ToPartyID
GO

—————————————————–
SELECT p1.FromPartyID, p1.ToPartyId
FROM Party_merge p1
LEFT OUTER JOIN Party_merge p2
ON p1.ToPartyID = p2.FromPartyID
WHERE p1.FromPartyID <> p1.ToPartyID
———————————–

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s