Selecting immediate parent data if child is not available
-
ParentChildMapping
ChildID ParentID
1 0
2 1
3 1
4 2
5 2
6 2
7 4
ParentId = 0 is the root of the tree
DataTable
ID Name somedata
1 A xxx
2 B yyy
5 C zzz
Now if I query data for ID=5, then it will return 5,C,zzz
If ID=6, it should return 2,B,yyy
If ID=7, it should return 2,B,yyy
ie., If data is not there in the child level then I need to search for immediate parent and so on till I end up with valid data.
Note: The root level object will always have the data.
Can anyone please help me in writing a query for the same?
-
create table #test (a int,b int)
insert into #test values(1,0),(2,1),(3,1),(4,2),(5,2),(6,2),(7,4)
create table #datatable(
D int, Name varchar(20),somedata varchar(20))
insert into #datatable values
(1 ,'A' ,'xxx'),
(2 ,'B' ,'yyy'),
(5 ,'C' ,'zzz')
declare @b-2 int =null
declare @id int=5
declare @i int=0
while (@i<3)
begin
select @id= ISnull((select d from #datatable where D=@id),(select b from #test where a=@id ))
select @b-2=d from #datatable where d=@id
if(@b is not null)
set @id = @b-2
set @i=@i+1
end
select * from #datatable where d=@id
it goes for 2 level hierarchy only, but can be increased based on data distribution.
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm: -
declare @b-2 int =null
declare @id int=6
declare @i int=0
while (1=1)
begin
select @id= ISnull((select d from #datatable where D=@id),(select b from #test where a=@id ))
select @b-2=d from #datatable where d=@id
if(@b is not null)
break;
set @i=@i+1
end
select * from #datatable where d=@id
now this should go for any level ...
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm: -
DECLARE @ParentChildMapping TABLE(ChildID INT, ParentID INT);
INSERT INTO @ParentChildMapping(ChildID,ParentID)
VALUES(1,0),(2,1),(3,1),(4,2),(5,2),(6,2),(7,4);
DECLARE @DataTable TABLE(ID INT, Name VARCHAR(20),somedata VARCHAR(20));
INSERT INTO @DataTable(ID,Name,somedata)
VALUES (1 ,'A' ,'xxx'),(2 ,'B' ,'yyy'),(5 ,'C' ,'zzz');
DECLARE @ID INT;
SET @ID = 5;
--SET @ID = 6;
--SET @ID = 7;
WITH CTE AS (
SELECT ChildID,ParentID, 1 AS Level
FROM @ParentChildMapping
WHERE ChildID=@ID
UNION ALL
SELECT p.ChildID,p.ParentID, c.Level+1
FROM @ParentChildMapping p
INNER JOIN CTE c ON p.ChildID=c.ParentID
)
SELECT TOP 1 d.ID,d.Name,d.somedata
FROM CTE c
INNER JOIN @DataTable d ON d.ID=c.ChildID
ORDER BY c.Level;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply