December 19, 2014 at 6:55 am
Hi,
Below is the sample data,
with relation as (
select 1 as IdRelation, 1 as parent, 2 as child union all
select 2 as IdRelation, 1 as parent, 3 as child union all
select 3 as IdRelation,1 as parent, 4 as child union all
select 4 as IdRelation, 5 as parent, 6 as child union all
select 5 as IdRelation, 5 as parent, 7 as child union all
select 6 as IdRelation, 5 as parent, 8 as child )
Requirement :
If i pass parent as 1 the it has to get the output as
with relation as (
select 1 as Userid union all
select 2 as Userid union all
select 3 as Userid union all
select 4 as Userid )
the logic is , i need to bring parent and child together. Likewise, if i pass child as 3 it has to bring the output as
with relation as (
select 1 as Userid union all
select 2 as Userid union all
select 3 as Userid union all
select 4 as Userid )
would like to aware of most efficient way to achieve this. Any sample query will be useful to understand.
thanks
December 19, 2014 at 7:10 am
Below is my try with formulated data
Declare @Id int = 3;
declare @t table (IdRelation int, Parent int,Child int)
insert into @t
select data.IdRelation, data.parent,data.child from (
select 1 as IdRelation, 1 as parent, 2 as child union all
select 2 as IdRelation, 1 as parent, 3 as child union all
select 3 as IdRelation,1 as parent, 4 as child union all
select 4 as IdRelation, 5 as parent, 6 as child union all
select 5 as IdRelation, 5 as parent, 7 as child union all
select 6 as IdRelation, 5 as parent, 8 as child ) data
If EXISTS(select 1 from @t where parent = @Id)
BEGIN
select child as UserId from @t where parent = @Id union all
select @Id as UserId -- as parent exists, so need of selecting from table
END
ELSE If EXISTS(select 1 from @t where child = @Id)
BEGIN
select parent as UserId from @t where child = @Id union all
select child as UserId from @t where parent =
(select parent as UserId from @t where child = @Id)
END
is there any best way to achieve this.
December 22, 2014 at 6:36 am
Hey guys,
Any help on this post. I am sure masters are busy for the Xmas- Event. I will be more happy if i get any suggestions.
Thakns
December 22, 2014 at 7:12 am
Finally i came up with
SELECT DISTINCT Parent
FROM @T WHERE Child = @Id OR Parent = @Id
UNION
SELECT DISTINCT B.Child
FROM @t A INNER JOIN @t B
ON A.Parent = B.Parent
WHERE A.Child = @Id OR A.Parent = @Id
any other suggestions.
December 22, 2014 at 8:06 am
You might want to try a Recursive CTE, check out BOL for examples, I think its Example D or E on the BOL WITH (common Table Expression) help page.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
December 22, 2014 at 5:16 pm
Here's a rundown of the various methods to traverse a hierarchy. You might want to try a couple of them to see what performs best for your specific situation.
https://www.simple-talk.com/sql/performance/the-performance-of-traversing-a-sql-hierarchy-/
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 23, 2014 at 1:44 pm
Thank you Dwain. nice example on the link.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply