SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Counting how many childs a parent category has

    A drop-down box has a list of all the "product categories" and it also shows all the children each "product category" has. Unfortuently the d/d box is way too long and is very confusing.

    What I'm trying to do is cut down the number of items in the d/down list and repetitive SQL queries. In order to do this I need to do a SQL query to count how many childs a parent category has.

    The table structure is thus;

    Code:
    # Pseduocode
    table product_category {
            var id (primary key)
            var parent_id (secondary key)
            var name (the name of the category)
    } #@end:table
    So, this would mean having to do a recursive SQL query to go through this table to find out how many childs a parent category has.

    Currently we use a php function and it recursively loads the function and spits out all the data, some of which is totally unnecessary.. All I need is the ultimate child of a parent (or where there is no more children) and return the COUNT variable.

    Is there a way to do this? I'm thinking it would be an inner join on itself? Is this right?

    I hope you can help.

    Thanks.

  2. #2
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here's what I have so far;

    Code:
    SELECT p.id, p.name, p.parent_id, count( c.id ) AS childcount
    FROM product_category AS p
    LEFT OUTER JOIN product_category AS c ON p.id = c.parent_id
    WHERE p.parent_id !=0
    GROUP BY p.id, p.name, p.parent_id
    ORDER BY `p`.`id` ASC
    However I can't seem to add the phrase "AND (count(c.id) = 0)" after the "WHERE p.parent_id !=0" bit...

    Is there a way to find only those product categories where there are no children?

    Thanks.

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    with the current way tou have your parent/child relationship set up, you can't do this in a single query.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    are you looking to count only the childless children only, all children only, all children and grandchildren (descendants), or only childless descendants?

    i guess the key question is, if you want to go down the tree more than one level, then you can't do it unless you also put a limit on the number of levels going down (each of which will require an additional LEFT OUTER self-join)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru worchyld's Avatar
    Join Date
    Jul 2003
    Location
    Newcastle upon Tyne
    Posts
    909
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've been able to resolve the problem using the query I posted before and reading your comments. Many thanks r937 and longneck.


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
  •