Results 1 to 2 of 2

Thread: help with query

  1. #1
    Join Date
    Jan 2004
    Posts
    1

    Unanswered: help with query

    Example table:

    +--------+-------+
    | Parent | Child |
    +--------+-------+
    | 17 | 16 |
    | 18 | 17 |
    | 19 | 18 |
    | 20 | 19 |
    | 21 | 20 |
    | 22 | 21 |
    +--------+-------+

    Now of course the numbers would be allot more random than that, but I have a table of id's that show a parent/child relationship. I am trying to construct a query where I input a id, say 22, and it will return a sequential list of all items that would be related, so in this example it would return:

    21
    20
    19
    18
    17
    16

    As 21 is a child of 22, 20 is a child of 21, etc ..

    Imagine kinda like a pyramid. Seems there should be an easy solution, I just can't picture it. :-\

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    there isn't

    if you want truly unlimited levels up or down, i.e. child to parent to grandparent, etc., or parent to child to grandchild, etc., then you must do this recursively from your scripting language

    only oracle, as far as i know, has this built in to the query language

    if you can be satisfied with results that are limited to N levels, then you can write a simple N+1 self-join using outer joins

    e.g. http://www.sitepoint.com/forums/showthread.php?p=975494
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •