SQLTeam.com | Weblogs | Forums

Sql question

So I have this table below

Id, SiteID, Name, ParentOrgId, Supervisor, SupvAlt1, SupvAlt2

44 33 x 5 bob lisa tammy
47 36 b 44 Jim tammy dave
55 36 ba 47 Dan amy george
56 36 baa 55 bert erney mike

I need do a select that will grab a record from an ID and also the parentOrgId before it untill it is a different SiteID

So if I passed it 56, it would get 55, then47, then 44

If you'll post usable data -- CREATE TABLE and INSERTs -- I'll write some code to help handle this.

Messaged you

hi

hope this link helps :slight_smile: :slight_smile:

hi

i tried to do this

hope it helps :slight_smile: :slight_smile:

drop create data ..
drop table #data 
go 

create table #data
(
id int ,
SiteID int ,
Name varchar(100),
ParentOrgId int,
Supervisor varchar(100),
SupvAlt1 varchar(100),
SupvAlt2 varchar(100)
)
go 


insert into #data select 44, 33, 'x' ,5 ,'bob' ,'lisa', 'tammy'
insert into #data select 47, 36, 'b' ,44, 'Jim','tammy','dave'
insert into #data select 55, 36, 'ba' ,47, 'Dan', 'amy', 'george'
insert into #data select 56, 36, 'baa' ,55,'bert', 'erney','mike'
go 

select * from #data
go
SQL ...
DECLARE @id INT = 56; 

WITH ctern 
     AS (SELECT * 
         FROM   #data 
         WHERE  id = @id 
         UNION ALL 
         SELECT b.* 
         FROM   ctern a 
                JOIN #data b 
                  ON a.parentorgid = b.id) 
SELECT * 
FROM   ctern 

go