find all relations - SQL Server Forums

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

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-12-12 : 01:54:11

Thanks, works perfect :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources