| How to SQL Select Query from Table With Parent and Child Relationship |
|
How to write a query that select record that has parent and child relationship.
ALTER PROCEDURE [dbo].[prEntityLibrarySelect] (@searchString varchar(200)) AS BEGIN
with [CTE](LibraryID, [Name], Description, LibraryLevel, LibraryParentID, LibraryTypeID) as ( select LibraryID, [Name], Description, LibraryLevel, LibraryParentID, LibraryTypeID from [tblEntityLibrary] c where (c.Name LIKE @searchString OR @searchString IS NULL) AND LibraryTypeID = 1 union all select c.* from [tblEntityLibrary] c INNER JOIN [CTE] p ON c.LibraryParentID = p.LibraryID ) select distinct * from [CTE] OPTION ( MAXRECURSION 0 )
END The MAXRECURSION is set to 0 for infinite level of recursion.
|
