November 11, 2002 at 10:36 pm
Hi All
In Oracle u have the concept of making a PL-SQL statement like start with and conect by clause for determining the child of the parent rows ie something like Employee boss relationship, is something is possible in SQL Server 2000. If there please let me know
Regarsds
Jay
November 12, 2002 at 2:33 am
Not possible no CONNECT BY equivalent in SQL.
Solution I tend to go for is a while loop inserting into a table variable and then joining on the table variable to get the complete results set.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
November 12, 2002 at 10:14 am
I love the CONNECT BY clause 🙂 Hopefully SQL Server will implement something similar. I ran into the same problem some time ago. My solution was to create a function with all of the parents and return the list in a table. I used an IN statement to obtain the list.
I am looking for my code now....
"Keep Your Stick On the Ice" ..Red Green
November 12, 2002 at 10:19 am
I created a function to do a recursive check, starting at the top node and going down to pull back all the children and parent. The function requires the parent node to start with and returns a table that can be selected from. A real live example could be used like this:
SELECT *
FROM Toys
WHERE Category in (SELECT * FROM fn_Subcategories('BabyToys')
Here's the code to the function.....
DROP FUNCTION fn_children
GO
CREATE FUNCTION fn_children (@Root INT)
RETURNS @retChildren TABLE (child int)
AS
BEGIN
DECLARE @children TABLE (child int,
level int)
DECLARE @Level int
SET @Level = 1
INSERT INTO @children VALUES (2, @Level)
WHILE EXISTS (SELECT 1 FROM childparent WHERE parent IN (SELECT child FROM @children WHERE level = @Level))
BEGIN
SET @Level = @Level + 1
INSERT INTO @children
SELECT child, @Level
FROM childparent
WHERE parent IN (SELECT child
FROM @children
WHERE level = @Level - 1)
END
--
INSERT INTO @retChildren
SELECT child FROM @children
RETURN
END
GO
"Keep Your Stick On the Ice" ..Red Green
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply