Post reply

I want to write validation Query which should return invalid parents if any....

  • May 29, 2013 at 11:54 pm

    #1619554

    Hi,

    you can use case statement.

  • May 30, 2013 at 12:43 am

    #1619566

    Hi

    I have tried 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

    But this query returns all parents that are at the top of the hierarchy. No parents of Their own. But my requirement is it should return all parents

    that don't have the link to themselves.

    It means I have to 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.

    Can you help me to write this query?

    Thanks..

  • May 30, 2013 at 4:33 am

    #1619610

    Hi Rishikesh,

    I have tried 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

    But this query returns all parents that are at the top of the hierarchy. No parents of Their own. But my requirement is it should return all parents

    that don't have the link to themselves.

    It means I have to 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.

    Can you help me to write this query?

    Thanks..

  • May 30, 2013 at 6:20 am

    #1619666

    Hi Manoj,

    You just need to union this query with

    ParentIntermediaryID != IntermediaryID and ParentIntermediaryID not in

    (SELECT ParentIntermediaryID FROM Intermediaries I1 INNER JOIN

    (

    SELECT DISTINCT ParentIntermediaryID

    FROM Intermediaries

    WHERE IntermediaryID = ParentIntermediaryID

    ) I2

    ON I1.IntermediaryID = I2.ParentIntermediaryID

    AND I1.ParentIntermediaryID IS NULL

    )

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply