11 Replies Latest reply on Mar 13, 2007 6:07 AM by CFMXPrGrmR

    Query output help: Display self-referencing table

    CFMXPrGrmR Level 2
      Hello,

      I'm trying to display in a cfoutput a self-referencing table. I'll start by showing some of the data in the table.

      There are only 3 columns, pl_id (auto increment id pri-key), pl_name and pl_parent_id (it's 0 if it's a parent, otherwise it's the pl_id value for the parent).

      pl_id pl_name pl_parent_id
      1 Country 0
      2 Food 0
      3 US 1
      4 Japan 1
      5 Hamburger 2
      6 Idaho 3
      7 Florida 3
      8 Cheese 2

      What I'm trying to output is something like this:
      Country - US - Idaho (3 levels here) or
      Country - US - Florida (or if there are only 2 levels like the next line)
      Food - Cheese

      I've tried using a cfoutput with a cfloop as well as grouping but not having any luck. Could someone clear my clouded head on this one?

      thanks much,

      Joe

      ps. Adobe should really use a fixed width font for these forums, it's impossible to line up table info!