Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2014
    Posts
    2

    Unanswered: Hierarchical Tree Query

    I am trying to get a print out of a nested hierarchical tree from a database.
    The database name is "collection" and the table name I am trying to query is "storage".
    The column in "storage" which lists the main and sub locations is named "idno" which has "museum" as the highest level, followed by the rooms.
    I have searched the web for the mysql syntax for this query but cannot get any methods to work, obviously I am doing something wrong.
    This is the mysql query I found on the web that I have been trying to use.

    mysql> SELECT idno FROM storage AS node, (used the column name as node.name and table name as nested_category)
    > storage AS parent
    > WHERE node.lft BETWEEN parent.lft AND parent.rgt
    > group by idno
    > order by group_concat(museum ORDER BY parent.lft); (used highest level in tree i.e museum as parent.name)

    I have inserted my "names" where I understood they should be.

    I opened PHPMYADMIN, selected the database and clicked on the storage table to display the columns and their content.
    When I run the query it gives me an error telling me that "idno" is ambiguous.
    I then tried running the query using "SELECT storage.idno..FROM....." and I get the error - storage.idno column name does not exist
    I tried just opening the database (not clicking on "storage" table and get the same result.)
    I am a novice when it comes to mysql so any help would be much appreciated.
    Eric

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are self joining a table then use an alias on the table Name(s) so that its explicit which table(s) you are referring to when you use a column name that is in more than one of the tables in the list.

    have a look at this
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2014
    Posts
    2

    Hierarchical Tree Query - solved

    Thanks healdem,
    Following your suggested link and more reading I finally got my result using -
    SELECT node.idno # idno = Column name
    FROM storage AS node, storage AS parent # storage = Tablename
    BETWEEN parent.hier_left
    AND parent.hier_right
    GROUP BY parent.idno
    ORDER BY group_concat( parent.idno
    ORDER BY parent.hier_left )

Posting Permissions

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