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.

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 hope 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
    
Are you looking for a condition similar to IntermediaryPK = ParentIntermediaryPK ? –  Praveen Prasannan May 14 '13 at 9:03
    
Hi Praveen...Thanks for reply... I just want to write a query which will validate each parent intermediary is also a parent to itself. Can you give me the query I will check it once.... Thanks..... –  ramu May 14 '13 at 9:56
1  
The wording of your question is too vague. It allows multiple interpretations, which could lead to multiple mutually contradictory answers. –  Walter Mitty May 14 '13 at 10:04
2  
Please provide some sample data and your required result –  Praveen Prasannan May 14 '13 at 10:15
    
Hi Walter Actually for validation I have to write query in format like: SELECT X FROM Y WHERE Z IS NULL.....to validate each parent intermediary's parent is that parent intermediary itself.... –  ramu May 14 '13 at 10:24

1 Answer 1

Select * 
from table 
where IntermediaryPK = ParentIntermediaryPK;

Or

Select * 
from table 
where IntermediaryID = ParentIntermediaryID;
share|improve this answer
    
Actually for validation I have to write query in format like: SELECT X FROM Y WHERE Z IS NULL –  ramu May 14 '13 at 10:14
    
Hi Praveen Here is the sample data intermediarypk intermediaryid parentintermediaryid parentintermediarypk grandparentintermediarypk 37 2003840093340001 NULL NULL NULL 38 200279839266 NULL NULL NULL 39 201995093279 NULL NULL NULL 40 200003615409 201951376189 4005 NULL 41 201336769172 201951376189 4005 NULL 42 201441882733 201282938485 6460 NULL .... ..... –  ramu May 15 '13 at 7:00
    
and your desired result is what? –  Praveen Prasannan May 15 '13 at 7:30
    
This query should not give any output. It should only validate each parent intermediary is also a parent to itself. foe ex. If I want to 'Validate each client has a valid gender' I hace written a query which is: SELECT * FROM CLIENT WHERE ISNULL(ClientGenderName,'') NOT IN (SELECT ISNULL(Gender,'') FROM PARTY_DETAILS) So the query format will be like this for our task, and this query will not give any output, It just give column names.... –  ramu May 15 '13 at 8:38
    
I want to write validation Query which should return invalid parents I If any.. –  ramu May 27 '13 at 6:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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