hrvica5
asked on
mssql
Hi,
please help me..
I have table with 2 fields
id and link_id
Something like this
ID LINK_ID
1 3
2
3 4
4
When I am positioned on ID 1, I have to select all linked id's and it should be this:
ID LINK_ID
3 4
4
Thanks, H
please help me..
I have table with 2 fields
id and link_id
Something like this
ID LINK_ID
1 3
2
3 4
4
When I am positioned on ID 1, I have to select all linked id's and it should be this:
ID LINK_ID
3 4
4
Thanks, H
try customize this:
declare @tbl table
(
ID int,
LINK_ID int
)
insert into @tbl
values
(1, 3),
(2, null),
(3, 4),
(4, null);
declare @selectedID int = 1
;with cte as
(
select a.ID, a.LINK_ID
from @tbl a
inner join @tbl b
on a.ID = b.LINK_ID
Where b.ID = @selectedID
), cte2 as
(
select a.ID, a.LINK_ID
from @tbl a
inner join cte b
on a.ID = b.LINK_ID
)
Select a.*
from cte a
union
Select b.*
from cte2 b
order by 1, 2
Here
declare @tbl table
(
ID int,
LINK_ID int
)
insert into @tbl
values
(1, 3),
(2, null),
(3, 4),
(4, 7),
(7, null);
declare @selectedID int = 1
;WITH items AS (
SELECT ID, LINK_ID
, 0 AS Level
, CAST(LINK_ID AS VARCHAR(255)) AS Path
FROM @tbl
WHERE ID = @selectedID
UNION ALL
SELECT i.ID, i.LINK_ID
, Level + 1
, CAST(Path + '.' + CAST(i.LINK_ID AS VARCHAR(255)) AS VARCHAR(255))
FROM @tbl i
INNER JOIN items itms ON itms.LINK_ID = i.ID
)
SELECT * FROM items ORDER BY ID, LINK_ID
ID LINK_ID Level Path
1 3 0 3
3 4 1 3.4
4 7 2 3.4.7
7 NULL 3 NULL
@Ryan Chong
your solution does not work for my sample data and giving
your solution does not work for my sample data and giving
ID LINK_ID
3 4
4 7
your solution does not work for my sample data and givingwell, that's depends on the asker's requirement.
obviously my solution is pulling for next 2 levels of data based on ID / LINK ID relationship.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you
Check it with below query.
Open in new window