SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Aug 2005
    Location
    Houston, TX
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Arrow Nested Set Model Query Question - Depth and Count of Nodes

    Hello,

    I'm working on the tutorial at http://dev.mysql.com/tech-resources/...ical-data.html.

    This is the query:

    Code MySQL:
    SELECT node.name, (COUNT(parent.name) - 1) AS depth
    FROM nested_category AS node,
    nested_category AS parent
    WHERE node.lft BETWEEN parent.lft AND parent.rgt
    GROUP BY node.name
    ORDER BY node.lft;

    This is the result:
    Code MySQL:
    name	             depth
    ELECTRONICS     0
    TELEVISIONS      1
    TUBE                 2
    LCD                   2
    PLASMA              2
    PORTABLE ELECTRONICS   1
    MP3 PLAYERS      2
    FLASH                3
    CD PLAYERS        2
    2 WAY RADIOS    2

    How would I modify the query to find out how many items are at each depth? Below is an example of what I would expect from the results:

    Code MySQL:
    Depth      Number
    0                 1
    1                 2
    2                 6
    3                 1


    Thanks in advance,

    Tim

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wrap an outer query around your current one as a derived table:

    Code:
    SELECT 
      depth, 
      count(*) as total
    FROM
      (
      SELECT 
        node.name, 
        (COUNT(parent.name) - 1) AS depth
      FROM nested_category AS node,
        nested_category AS parent
      WHERE 
        node.lft BETWEEN parent.lft AND parent.rgt
      GROUP BY node.name
      ) as derived_table
    GROUP BY 
      depth

  3. #3
    SitePoint Enthusiast
    Join Date
    Aug 2005
    Location
    Houston, TX
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you very much!

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,274
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    awesome... you're my go-to guy for nested sets now, guelphdad
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Look just because I've got Celko books sitting on my bookshelf doesn't mean I can't learn elsewhere! :-) Plus the OP already did the hard work.

  6. #6
    SitePoint Enthusiast
    Join Date
    Aug 2005
    Location
    Houston, TX
    Posts
    86
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    The example shows how many items are at each depth starting from the top or depth of zero (Electronics). How would the query be changed if I wanted to start counting from Television instead of Electronics? Essentially, I'd like Television to be considered at depth zero and start counting depth from there.

    I tried editing the query but had no success.

    Hope my question makes sense!

    Thanks,

    Tim


Bookmarks

Posting Permissions

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