Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 SQL Server Administration (2012)
 find all relations

Author  Topic 

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-12-11 : 05:16:54
Hi

I have a table that have a relations between ID's. The table look like this...


CREATE TABLE [dbo].[tbl_Products](
[NodeId] [int] IDENTITY(1,1) NOT NULL,
[ParentNodeId] [int] NULL
CONSTRAINT [PK_tbl_Products] PRIMARY KEY CLUSTERED
(
[NodeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


ParentNodeID is related to NodeID, I would like to get all rows that are related based on a NodeID.


Can someone show me how to do that?


Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-12-11 : 20:39:34
[code]declare @NodeID int = 1234;

;with iterProducts
as (
select *
from dbo.tbl_Products p
where p.NodeID = @NodeID

union all

select parent.*
from dbo.tbl_Products parent
inner join
iterProducts child
on parent.NodeID = child.ParentNodeID
where
child.ParentNodeID is not NULL -- I don't see how this is needed but it doesn't hurt
)
select *
from iterProducts[/code]Recursive CTEs follow a coding pattern:
1) Find the initial value(s)
2) UNION ALL
3) Find the "next" value(s) using the CTE
4) Define how to stop looking, usually in the WHERE clause



No amount of belief makes something a fact. -James Randi
Go to Top of Page

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-12-12 : 01:54:11
Thanks, works perfect :)
Go to Top of Page
   

- Advertisement -