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 re开发者_高级运维cursive queries, I have nothing left to solve this one. Please help :)

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 .

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

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