Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: Displaying a tree structure - HELP

    Hi,

    I have the following parent child tree structure in a database table called tbl_locations:

    Code:
    Location_ID	Location_Name	Location_Parent_ID	Location_Root_ID
    
    1		UK		0			0
    2		East Anglia	1			1
    3		Cambridgeshire	2			1
    4		Norfolk		2			1
    5		East Midlands	1			1
    6		Derbyshire	5			1
    7		Lincolnshire	5			1
    8		EU Countries	0			0
    9		Germany		8			8
    10		France		8			8
    11		west Midlands	1			1
    12		Birmingham	1			11
    13		Shropshire	1			11
    14              Suffolk         2			1
    I need to retrieve the location_id and location_name of all records in this table and populate a dropdown select box so that the list is displayed and indented as follows:

    Code:
    UK
      East Anglia
        Cambridgeshire
        Norfolk
        Suffolk
      East Midlands
        Derbyshire
        Lincolnshire
      West Midlands
        Birmingham
        Shropshire
    EU Countries
      France
      Germany	
    Other Countries
      America
      Canada
    I've searched all of over to achieve this but unable find any examples. It seems like an easy task if you can retrieve a database result set which can order the list as above. But since its a parent, child tree table I am unable to produce such a result set with my level and experience of SQL. Any help would be appreciated

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im pretty certain you will find what you want with a class available from t'web
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ah, you wanted indentation, too
    Code:
    SELECT one.Location_Name
         , one.Location_ID AS sortkey1
         , CAST(NULL AS UNSIGNED) AS sortkey2
         , CAST(NULL AS UNSIGNED) AS sortkey3
      FROM tbl_locations AS one
     WHERE one.Location_Parent_ID = 0
    UNION ALL
    SELECT CONCAT('  ',two.Location_Name)
         , one.Location_ID
         , two.Location_ID
         , NULL
      FROM tbl_locations AS one
    INNER
      JOIN tbl_locations AS two
        ON two.Location_Parent_ID = one.Location_ID
     WHERE one.Location_Parent_ID = 0
    UNION ALL
    SELECT CONCAT('    ',thr.Location_Name)
         , one.Location_ID
         , two.Location_ID
         , thr.Location_ID
      FROM tbl_locations AS one
    INNER
      JOIN tbl_locations AS two
        ON two.Location_Parent_ID = one.Location_ID
    INNER
      JOIN tbl_locations AS thr
        ON thr.Location_Parent_ID = two.Location_ID
     WHERE one.Location_Parent_ID = 0
    ORDER
        BY sortkey1
         , sortkey2
         , sortkey3
    see also http://www.dbforums.com/ansi-sql/163...rder-list.html

    sorry about the CONCAT function above, i tested this in mysql, even though your original question was in the ANSI SQL forum

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Mar 2007
    Posts
    212
    The above wont work because it wont order the list alphabetically. It just outputs the list in the same order it is stored.

    I need UK listed at the top, then its regions alphabetically, then each sub-regions alphabetically, then EU Countries and all EU Countries alphabetically, then Other Countries and all other countries alphabetically.

    see also your results set on SQL Query to order a list

    Thanks for the try though.
    Last edited by ozzii; 01-23-09 at 16:30.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    how come you haven't tried changing what i gave you?

    there are two ways to deal with non-standard sequences:

    1. add a "sortkey" column to the table, and use that

    2. hard-code the special cases in the ORDER BY clause


    Code:
    SELECT one.Location_Name
         , one.Location_Name AS sortkey1
         , CAST(NULL AS UNSIGNED) AS sortkey2
         , CAST(NULL AS UNSIGNED) AS sortkey3
      FROM tbl_locations AS one
     WHERE one.Location_Parent_ID = 0
    UNION ALL
    SELECT CONCAT('  ',two.Location_Name)
         , one.Location_Name
         , two.Location_Name
         , NULL
      FROM tbl_locations AS one
    INNER
      JOIN tbl_locations AS two
        ON two.Location_Parent_ID = one.Location_ID
     WHERE one.Location_Parent_ID = 0
    UNION ALL
    SELECT CONCAT('    ',thr.Location_Name)
         , one.Location_Name
         , two.Location_Name
         , thr.Location_Name
      FROM tbl_locations AS one
    INNER
      JOIN tbl_locations AS two
        ON two.Location_Parent_ID = one.Location_ID
    INNER
      JOIN tbl_locations AS thr
        ON thr.Location_Parent_ID = two.Location_ID
     WHERE one.Location_Parent_ID = 0
    ORDER
        BY CASE WHEN sortkey1 = 'UK' THEN 1
                WHEN sortkey1 = 'EU Countries' THEN 2
                ELSE 3 END
         , sortkey2
         , sortkey3
    results:
    Code:
    Location_Name       sortkey1       sortkey2        sortkey3
    UK                  UK                  
      Birmingham        UK             Birmingham         
      East Anglia       UK             East Anglia         
        Cambridgeshire  UK             East Anglia     Cambridgeshire
        Norfolk         UK             East Anglia     Norfolk
        Suffolk         UK             East Anglia     Suffolk
      East Midlands     UK             East Midlands         
        Derbyshire      UK             East Midlands   Derbyshire
        Lincolnshire    UK             East Midlands   Lincolnshire
      Shropshire        UK             Shropshire         
      west Midlands     UK             west Midlands         
    EU Countries        EU Countries                  
      France            EU Countries   France         
      Germany           EU Countries   Germany
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by r937
    how come you haven't tried changing what i gave you?

    there are two ways to deal with non-standard sequences:

    1. add a "sortkey" column to the table, and use that

    2. hard-code the special cases in the ORDER BY clause
    Rudy you are one smart dude! The reason why I haven't tried changing what you've given me is because my SQL knowledge is limited but that might all change once i've purchased your book - Simply SQL!

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks for the kind words

    i guess the tricky part was to see how the unions were constructed

    first the top level nodes, then the second level nodes, etc., all done with INNER JOINs

    the ORDER BY works nicely because NULLs sort first, so you can see why the NULLs are used as placeholders in the sortkeys for the level one SELECT, right?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by r937
    thanks for the kind words

    i guess the tricky part was to see how the unions were constructed

    first the top level nodes, then the second level nodes, etc., all done with INNER JOINs

    the ORDER BY works nicely because NULLs sort first, so you can see why the NULLs are used as placeholders in the sortkeys for the level one SELECT, right?

    why wont the original query work if i replace the order by using a case statement and by replacing the Location_name with its Location_ID instead?

    Code:
    SELECT one.Location_Name
         , one.Location_ID AS sortkey1
         , CAST(NULL AS UNSIGNED) AS sortkey2
         , CAST(NULL AS UNSIGNED) AS sortkey3
      FROM tbl_locations AS one
     WHERE one.Location_Parent_ID = 0
    UNION ALL
    SELECT CONCAT('  ',two.Location_Name)
         , one.Location_ID
         , two.Location_ID
         , NULL
      FROM tbl_locations AS one
    INNER
      JOIN tbl_locations AS two
        ON two.Location_Parent_ID = one.Location_ID
     WHERE one.Location_Parent_ID = 0
    UNION ALL
    SELECT CONCAT('    ',thr.Location_Name)
         , one.Location_ID
         , two.Location_ID
         , thr.Location_ID
      FROM tbl_locations AS one
    INNER
      JOIN tbl_locations AS two
        ON two.Location_Parent_ID = one.Location_ID
    INNER
      JOIN tbl_locations AS thr
        ON thr.Location_Parent_ID = two.Location_ID
     WHERE one.Location_Parent_ID = 0
    ORDER BY
        CASE WHEN sortkey1 = 1 THEN 1
                WHEN sortkey1 = 8 THEN 2
                ELSE 3 END
         , sortkey2
         , sortkey3
    Also how would you re-order the list so that UK and all its subregions are ordered alphabetically as below and then Scotland and its sub regions, Wales and its sub regions and Ireland and it sub regions are listed below all the other UK subregions as show below:

    Code:
    UK
      East Anglia
        Cambridgeshire
        Norfolk
        Suffolk
      East Midlands
        Derbyshire
        Lincolnshire
      West Midlands
        Birmingham
        Shropshire
      Scotland
        Angus
        Fife
      Wales
        Cardiff
        Gwent
      Ireland
        Carlow
        Dublin
    EU Countries
      France
      Germany	
    Other Countries
      America
      Canada
    Would the entire query need to be changed or could it be handled by hard-coding the special cases in the ORDER BY clause?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you can do this by judiciously choosing the values in the sort keys

    for instance, in the example you just posted, you have Location_ID as sortkey2 and sortkey3, so within each sortkey1, you won't get alphabetic like you wanted
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by r937
    you can do this by judiciously choosing the values in the sort keys

    for instance, in the example you just posted, you have Location_ID as sortkey2 and sortkey3, so within each sortkey1, you won't get alphabetic like you wanted
    I've been working on this all day without any luck. When you say it can be done by choosing values in the sort keys do you mean in the order by CASE statement?

    Your gonna need to give me more hints on this! I've tried all manner of combinations choosing various values for the sort keys within the case statement below without any luck - Scotland, Wales, Ireland and their subregions just wont order underneath all the other Uk regions.

    Code:
    SELECT one.Location_Name
         , one.Location_Name AS sortkey1
         , CAST(NULL AS UNSIGNED) AS sortkey2
         , CAST(NULL AS UNSIGNED) AS sortkey3
      FROM tbl_locations AS one
     WHERE one.Location_Parent_ID = 0
    UNION ALL
    SELECT CONCAT('  ',two.Location_Name)
         , one.Location_Name
         , two.Location_Name
         , NULL
      FROM tbl_locations AS one
    INNER
      JOIN tbl_locations AS two
        ON two.Location_Parent_ID = one.Location_ID
     WHERE one.Location_Parent_ID = 0
    UNION ALL
    SELECT CONCAT('    ',thr.Location_Name)
         , one.Location_Name
         , two.Location_Name
         , thr.Location_Name
      FROM tbl_locations AS one
    INNER
      JOIN tbl_locations AS two
        ON two.Location_Parent_ID = one.Location_ID
    INNER
      JOIN tbl_locations AS thr
        ON thr.Location_Parent_ID = two.Location_ID
     WHERE one.Location_Parent_ID = 0
    ORDER
        BY CASE WHEN sortkey1 = 'UK' THEN 1
                WHEN sortkey1 = 'EU Countries' THEN 2
                ELSE 3 END
         , sortkey2
         , sortkey3

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    could you dump your data for me please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by r937
    could you dump your data for me please
    See attached text file for dump.
    Attached Files Attached Files

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    ORDER
        BY CASE WHEN sortkey1 = 'UK' THEN 1
                WHEN sortkey1 = 'Other European Countries' THEN 2
                ELSE 3 END
         , CASE WHEN sortkey2 = 'Scotland' THEN 7
                WHEN sortkey2 = 'Wales' THEN 8
                WHEN sortkey2 = 'Ireland' THEN 9
                ELSE 1 END
         , sortkey2
         , sortkey3
    you might also consider using England at level 2 under UK, similar to Scotland, but then you'd have to change the CASE expressions again
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by r937
    Code:
    ORDER
        BY CASE WHEN sortkey1 = 'UK' THEN 1
                WHEN sortkey1 = 'Other European Countries' THEN 2
                ELSE 3 END
         , CASE WHEN sortkey2 = 'Scotland' THEN 7
                WHEN sortkey2 = 'Wales' THEN 8
                WHEN sortkey2 = 'Ireland' THEN 9
                ELSE 1 END
         , sortkey2
         , sortkey3
    you might also consider using England at level 2 under UK, similar to Scotland, but then you'd have to change the CASE expressions again
    I think I understand how this query works now - although it did take me an entire day messing around with it! How do you work out the order 7, 8, 9 though for sortkey2? I thought these were another way of referencing the column positions or have got my SQL wrong? I know I have 9 subregions before you get to Scotland has that got something to do with it?
    Last edited by ozzii; 01-24-09 at 17:06.

  15. #15
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    no, 7, 8, and 9 are only values used to create a pseudo-column that is then used for sorting

    the ordinal numbers that are sometimes used in ORDER BY clauses are not used in the query so far, but are used in the query below

    if you want to see the difference in how they work, let's put the CASE expressions into the SELECT clause so you can see what they produce
    Code:
    SELECT CASE WHEN sortkey1 = 'UK' THEN 1
                WHEN sortkey1 = 'Other European Countries' THEN 2
                ELSE 3 END      AS first_sort_column
         , CASE WHEN sortkey2 = 'Scotland' THEN 7
                WHEN sortkey2 = 'Wales' THEN 8
                WHEN sortkey2 = 'Ireland' THEN 9
                ELSE 1 END      AS second_sort_column
         , sortkey2 AS third_sort_column         
         , sortkey3 AS fourth_sort_column
         , dddd.*
      FROM (
    
           /* put the entire UNION query here,
              without the ORDER BY clause  */
    
           ) AS dddd
    ORDER
        BY 1 -- first_sort_column
         , 2 -- second_sort_column
         , 3 -- third_sort_column
         , 4 -- fourth_sort_column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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