SELECT child and grand child objects from given parent

Question

SELECT child and grand child objects from given parent

Magnus T

Valued Member

Points: 54
More actions
August 6, 2007 at 6:58 am

#118071

Hi I have a question on how I can make a single table SELECT query to get
child object from my table.
CatId   ParentId   Description
1        1             BASE
2        1             Car
3        1             Motorcycle
4        2             Sedan
5        2             Stationwagon
6        2             Convertible
7        3             Racer
8        3             Custom
9        3             Allround
10      4             Luxury
11      4             Middleclass
12      4             Compact
When making a search from a form I select car(2) in I want to get a resultset that contains all childobject and grand child objects in the the result set.
Could anyone give me a good example how to do that?
Thanks

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

James Moore (Red Gate) SSCrazy Points: 2644 More actions · Answer 1

Hi,

If you are using SQL Server 2005 you can do this very nicely with CTE's (Common Table Expressions) - have a look at http://www.sqlservercentral.com/columnists/sSampath/recursivequeriesinsqlserver2005.asp - if you are using SQL 2000 it is slightly more convoluted.

Let me know if you get stuck or are using SQL 2000.

James

--
James Moore
Red Gate Software Ltd

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 2

Here's a slightly modified function that I use in these instances (modified to fit your example table, but not tested). You have to ensure that you will not have any circular references.

CREATE FUNCTION dbo.ExplodeList(@id int)RETURNS @List_TABLE TABLE (Lvl int, CatID int, ParentID int, [Description] VARCHAR(50))AS
BEGIN  DECLARE @lvl int  INSERT INTO @List_Table     SELECT 0, CatID, ParentID, [Description]    FROM dbo.Table_1    WHERE CatID = @id  SET @lvl = 0  WHILE(@@ROWCOUNT>0)  BEGIN    SET @lvl = @lvl + 1    INSERT INTO @List_Table      SELECT         @lvl, t1.CatID, t1.ParentID, t1.[Description]      FROM         dbo.Table_1 t1 INNER JOIN dbo.Table_1 t2           ON t2.catID=t1.parentID      WHERE         t2.CatID IN ( SELECT CatID FROM @List_Table WHERE lvl=@lvl-1 )  END  RETURNEND

You would use this function in your example by running this query: SELECT * FROM dbo.ExplodeList(2)

Hope this helps

Mark