January 24, 2012 at 9:57 am
tableA
OrgId ParentId
11
23
32
41
51
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
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
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
January 24, 2012 at 10:24 am
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