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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy