CODEWITHSUNDEEP
sqlsql-server
ChandlerPelhams
ChandlerPelhams

Reputation: 1678

SQL Query to find Parent-Child Child-Parent relationships?

o_O

If I have the following records in a table:

Parent     Child
1          2 <--
2          1 <--
3          2
3          4

etc...

And I want to identify records that are both the parent of their child AND the child of their parent such as the 2 records identified by arrows above, how would I accomplish this?

I am trying to run some recursive SQL on this table, but these items are causing an infinite loop. I would like to identify these items so they can be addressed manually.

My brain is fried-enough from messing with recursive queries, I have nothing left to solve this one. Please help :)

Upvotes: 3

Views: 11653

Answers (3)

Lamak
Lamak

Reputation: 70638

DECLARE @YourTable TABLE (Parent INT, Child INT)

INSERT INTO @YourTable
SELECT 1, 2
UNION
SELECT 2, 1
UNION
SELECT 3, 2
UNION
SELECT 3, 4


SELECT *
FROM @YourTable A
INNER JOIN @YourTable B
ON A.Parent = B.Child AND A.Child = B.Parent

Upvotes: 1

Jon Egerton
Jon Egerton

Reputation: 41549

The following query will work in your example case. If it needs more you'll have to extend the demonstration information

;WITH CTE_DATA AS (
    Select Parent = 1, Child = 2
    union Select Parent = 2, Child = 1
    union Select Parent = 3, CHild = 2
    union Select Parent = 3, Child = 4
)
select 
    d1.*
from
    CTE_DATA d1
    join CTE_DATA d2 on d1.Child = d2.Parent and d2.Child = d1.Parent

Upvotes: 2

a1ex07
a1ex07

Reputation: 37364

If understood you well, you don't need recursion at all:

 SELECT a.parent, a.child
 FROM table1 a
 INNER JOIN table1 b ON (b.child=a.parent and a.child = b.parent)

You might want to use LEFT JOIN instead of INNER if you also need to display rows that don't satisfy condition .

Upvotes: 6

Related Questions