Link to home
Start Free TrialLog in
Avatar of hrvica5
hrvica5Flag for Croatia

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
Avatar of Vishal Tankariya
Vishal Tankariya
Flag of India image

Hi,

Check it with below query.

select * from tbl1 where ID in(select linkid from tbl1) order by id asc

Open in new window

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

Open in new window

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

Open in new window

@Ryan Chong

your solution does not work for my sample data and giving
ID	LINK_ID
3	4
4	7

Open in new window

your solution does not work for my sample data and giving
well, 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
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of hrvica5

ASKER

thank you