Recursive Queries

• ryan james

  Valued Member

  Points: 57

  More actions

  March 24, 2006 at 8:35 am

  #171712

  Hi all,

  Im new to recursive relationships and have been reading the posts on Hierarchies and the stored procedures needed, but have not found the answere to my problem.

  I have the following tables:

  Part (part_id, part_name.....)

  Parent_part (part_id, parent_part_id) --// both fields are forign keys of table part

  As you can guess the relationship between Part and Parent_part has pontentialy many levels, but for now I’m just trying to display/query for one level only. Ok, I'm trying create a query to create the following query return:

  Parent_part.Parent_Part_ID	Part.Part_name (joining by Part.Part_id = Parent_part.parent_part_id)	Parent_Part.part_ID	Part.Part_name (joining by Parent_Part.part_id = Part.part_id)
  1	X-ray unit	3	X-ray bed
  		4	X-ray mat
  2	Sink	5	Tap
  		6	Basin

  Sorry to ask but is this possible? And any clues on the SQL statement need?

  Thanks, Ryan

   

   

• allen davidson

  SSCertifiable

  Points: 7042

  More actions

  March 24, 2006 at 9:10 am

  #628658

  I came up with this ... but I'm sure there is a better way. It's not recursive so only 'nests' to one level.

  Might get some more interest!

  CREATE TABLE #Part

  (ID INT ,

  PartName VARCHAR(255))

  INSERT INTO #Part VALUES (1,'Sink')

  INSERT INTO #Part VALUES (2,'Basin')

  INSERT INTO #Part VALUES (3,'Tap')

   

  CREATE TABLE #ParentPart

  (PartID INT,

  ParentPartID INT)

  INSERT INTO #ParentPart VALUES (2,1)

  INSERT INTO #ParentPart VALUES (3,1)

  SELECT

   p1.ID,

   p1.PartName,

   pp.PartID,

   p2.PartName

  FROM

   #Part p1 INNER  JOIN #ParentPart pp

    ON p1.ID = pp.ParentPartID

   INNER JOIN

    #Part p2

    ON pp.PartID = p2.ID

  WHERE p1.ID NOT IN (SELECT PartID FROM #ParentPart)