Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2004
    Posts
    4

    Unanswered: complex mysql query

    Hi all, here's my situation

    I have hierarchical data that can be an arbitrary number of levels deep. I'm representing it with the adjacency list model. So there are 2 tables

    Categories
    id, parentid, title

    and

    DOCUMENTS
    id, parentid, title, desc


    Now, here is what I am already doing successfully - I can retrieve a resultset which contains mixed results from both of these tables - essentially a union statement, though because i am forced to use mysql 3.23 i am using the dummy table method. The query looks like this...

    SELECT
    IFNULL(documents.id, categories.id) AS id,
    IFNULL(documents.parentid, categories.parentid) AS parentid,
    IFNULL(documents.title, categories.title) AS title,
    documents.desc,
    IF(categories.id, 1, 0) AS isCategory
    FROM _dummy AS D
    LEFT JOIN documents ON (D.num = 0 AND documents.parentid = $parentID)
    LEFT JOIN categories ON (D.num = 1 AND categories.parentid = $parentID)
    WHERE D.num < 2 AND IFNULL(documents.id, categories.id) IS NOT NULL


    This works like a charm. However, if possible I would also like to retrieve an additional field which is a COUNT() of the immediate children (documents and categories only one level deep) of each 'category' in the resultset. Mysql 3.23 doesn't support subselects, but I'm fairly certain this is possible with temporary tables. However, I'm not entirely sure how to go about it. (Have been beating my head against this for awhile, grin)

    If anyone has some suggestions, I'd be deeply appreciative. Currently I'm just looping through the resultset and executing a separate COUNT() on categories - which I'd prefer to avoid if possible.

    Thanks in advance for your help.
    Edit/Delete Message

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Do some research on "recursive descent functions"
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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