Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 )
share|improve this question

marked as duplicate by ypercube, dezso, Jon Seigel, StanleyJohns, Max Vernon May 28 '13 at 14:46

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

    
What have you tried? What poses a particular difficulty at this point? –  Andriy M May 28 '13 at 7:46
    
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
1  
Thank you! But please update your question instead of just replying to me. (Use the Edit link underneath it.) My point was that your question didn't sound well enough to attract any help, and by updating your question with what you have tried you will at the very least be letting the people know that you are not being lazy. Also, to help you to improve your question further, please elaborate (in your question) on which rows in your example should be returned by the query you are trying to build, and why. That's just to make sure the goal is clear. (I, for one, am not sure I've understood all.) –  Andriy M May 28 '13 at 9:45

1 Answer 1

The naming convention here is what gave me trouble the last time you posed this question. But basically you just want to return all values where ParentIntermediaryPK isn't null, and doesn't match the value of IntermediaryPK on the same row?

If I understood that right, it would be this simple?

SELECT * 
FROM Intermediary 
WHERE IntermediaryPK <> ParentIntermediaryPK 
AND IntermediaryPK IS NOT NULL
share|improve this answer
    
Hi Kahn, 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 11:40
    
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. –  ramu May 29 '13 at 10:17
    
I have written this query, And this query returns all parents that are at the top of the hierarchy. No parents of Their own. But my validation should return all parents that don't have the link to themselves... What correction I can make in this query? SELECT * FROM Intermediaries I1 INNER JOIN ( SELECT DISTINCT ParentIntermediaryID FROM Intermediaries WHERE IntermediaryID = ParentIntermediaryID ) I2 ON I1.IntermediaryID = I2.ParentIntermediaryID AND I1.ParentIntermediaryID IS NULL Thanks for your time.. –  ramu May 30 '13 at 5:10

Not the answer you're looking for? Browse other questions tagged or ask your own question.