0

I have a table that shows relations between records in a sort of "parent to child" fashion. An [ID] field identifies a record and a [PARENT] field identifies that record's parent by [ID]. This creates a tree structure for the top-most parent. See the below table as an example:

[ID]     [PARENT]     [QUANTITY]
123      123          1
456      123          2
789      123          1
321      456          1
654      321          2
987      321          4

Notice how record with [ID] = '123' is the top-most record in this "tree". I need to query this table to select all tree nodes (complete records) under the top-most record, for example all of the records beneath [ID] = '123' in the tree (all records in the above sample table). Currently I am using a stored procedure to loop grabbing the children of the record with [ID] = '123', then that records children, etc. I know there must be an easier query (not a stored proc) that could dynamically adapt to this situation I just am not sure how that might work without looping to ensure all children are received. Any advice is appreciated.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Sloth Armstrong
  • 1,066
  • 2
  • 19
  • 39
  • This is a hierichal query and in SQL server is accomplished using the syntax XML_Path Example: http://stackoverflow.com/questions/4256900/query-to-get-xml-output-for-hierarchical-data-using-for-xml-path-in-sql-server – xQbert Jul 09 '13 at 22:27
  • 1
    Search for `recursive parent child sql query` and you should get a bunch of hits. – Jon Crowell Jul 09 '13 at 22:30
  • Thanks everyone! I guess knowing what to search makes it incredibly easy. Appreciate it a lot. – Sloth Armstrong Jul 09 '13 at 22:54

1 Answers1

1

use "WITH" Common table expression - highly recommended on Hierarichal query.

http://msdn.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

RoMEoMusTDiE
  • 4,739
  • 1
  • 17
  • 26