# validate where each parent intermediary is also a parent to itself [duplicate]

This question is an exact duplicate of:

I want to validate where each parent intermediary is also a parent to itself. So this query should return invalid parents in SELECT If table has any.

I need to do is first of all get all parents records. This should be done by joining Intermediries to itself on ParentIntermediaryID = IntermediaryID (inner join). Now, from all the parents return those parents that don't have any rows with ParentIntermediaryID = IntermediaryID.

I hopa the requirement is clear. Can you give me query for this?

--===== Create the test table

CREATE TABLE Intermediary
(
IntermediaryPK INT ,
IntermediaryID NVARCHAR(20),
IntermediaryName NVARCHAR(200),
IntermediaryTypeID NVARCHAR(1),
ParentIntermediaryID NVARCHAR(20),
IntermediaryTypePK TINYINT,
ParentIntermediaryPK INT
GrandParentIntermediaryPK INT
GrandParentIntermediaryID NVARCHAR(20)
)


== Insert the test data into the test table

INSERT INTO Intermediary
(IntermediaryPK, IntermediaryID, IntermediaryName, IntermediaryTypeID, ParentIntermediaryID,
IntermediaryTypePK, ParentIntermediaryPK, GrandParentIntermediaryPK, GrandParentIntermediaryID)
SELECT 552, '200244584261', 'A', '1', '201841943403', 1, 6459, 6459, '201841943403' UNION ALL
SELECT 553, '200983879092', 'B', '1', '200707895681', 1, 6462, 6459, '200707895681' UNION ALL
SELECT 554, '200925413387', 'C', '1', '200707895681', 1, 6462, 6462, '200707895681' UNION ALL
SELECT 555, '200472620781', 'D', '1', '200707895681', 1, 6462, 6462, '200707895681' UNION ALL
SELECT 556, '201902784325', 'E', '1', '200707895681', 1, 6462, 6462, '200707895681' UNION ALL
SELECT 557, '201874832909', 'F', '1', '200707895681', 1, 566 , 6462, '200707895681' UNION ALL
SELECT 558, '201264024229', 'G', '1', '200707895681', 1, 566 , 6462, '200707895681' UNION ALL
SELECT 559, '201725870455', 'H', '1', '201062751762', 1, 566 , 6462, '200707895681'


I have tried with this query:

SELECT  *
FROM    Intermediary AS I1
WHERE   ParentIntermediaryPK IS NOT NULL
AND     NOT EXISTS (
SELECT  *
FROM    Intermediary AS I2
WHERE   I2.IntermediaryPK = I2.ParentIntermediaryPK
AND I1.ParentIntermediaryPK = I2.IntermediaryPK )


## marked as duplicate by ypercubeᵀᴹ, dezso, Jon Seigel, StanleyJohns, Max VernonMay 28 '13 at 14:46

This question was marked as an exact duplicate of an existing question.

• I want to write a query which will validate each parent intermediary is also a parent to itself. So this query should return invalid parents in SELECT If table has any. – ramu May 28 '13 at 8:34
• Well, you've restated your intention, thanks for that. But you haven't answered either of my questions. I'll be fair with you and repeat them as well: 1. What have you already tried? 2. What exactly is difficult for you at this point? What are you struggling with? Honestly, so far your question reads basically like this: "I want to do this thing but I don't know how to and so I won't do it. Please someone do that thing for me." I don't think this can attract many helping hands. – Andriy M May 28 '13 at 8:47
• Hi Andriy I have tried with this query: SELECT * FROM Intermediary AS I1 WHERE ParentIntermediaryPK IS NOT NULL AND NOT EXISTS ( SELECT * FROM Intermediary AS I2 WHERE I2.IntermediaryPK = I2.ParentIntermediaryPK AND I1.ParentIntermediaryPK = I2.IntermediaryPK ) – ramu May 28 '13 at 9:38
• For example we have Intermediaries ParentIntermediaries A F B A C D C E D F B Suppose If C has child and It does not have any parents, So C itself should have its parents or else C should be null.... So we have to validate like this.... – ramu May 28 '13 at 10:56

SELECT *