Post reply

Cross relationship.

  • January 24, 2012 at 9:57 am

    #254225

    tableA

    OrgId ParentId

    1 1

    2 3

    3 2

    4 1

    5 1

    how do I diplay relationship where OrgId 2 has its parent as 3 and Orgid 3 has its parent as 2.

    This is a bug but I need to retrieve that in query

  • January 24, 2012 at 9:59 am

    #1437785

    Without anymore description or sample data than that, it would just be:

    select *

    from tableA

    where (orgid = 2 and parentid = 3) or (orgid = 3 and parentid = 2)

    But I assume there is more to the problem than just that.

  • January 24, 2012 at 10:06 am

    #1437795

    As roryp says, you haven't given us enough information to give you a satisfactory answer.

    Please read this link[/url] about how best to provide us with working sample data and expected outcome.

    For now, does this help?

    BEGIN TRAN

    --First, let's create some sample data that people can use

    CREATE TABLE hierarchy (OrgId TINYINT, ParentId TINYINT)

    INSERT INTO hierarchy

    SELECT * FROM (VALUES(1,1),(2,3),(3,2),(4,1),(5,1))a(OrgId,ParentId)

    SELECT *

    FROM hierarchy main

    --Get child

    OUTER APPLY (SELECT

    OrgId AS childid

    FROM hierarchy

    WHERE main.OrgId = ParentId) child

    ROLLBACK

    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • January 24, 2012 at 10:24 am

    #1437806

    I tried this self join which seemed to work.

    select o.*

    from

    tableA o

    inner join

    tableA c

    on o.OrgId = c.ParentID

    and o.ParentID= c.OrgId

    where

    o.OrgId <> o.ParentID

Viewing 4 posts - 1 through 3 (of 3 total)

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