Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Question Unanswered: SQL Query to order a list

    Hi,

    I have a table called tbl_locations as follows:

    Code:
    CREATE TABLE `tbl_locations` (
      `Location_ID` int(10) unsigned NOT NULL auto_increment,
      `Location_Name` varchar(50) NOT NULL,
      `Location_Parent_ID` int(10) unsigned NOT NULL default '0',
      `Location_Root_ID` int(10) unsigned NOT NULL default '0',
      PRIMARY KEY  (`Location_ID`),
      UNIQUE KEY `Location_Name` (`Location_Name`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    And some sample data as follows:

    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

    Locations in red are main Locations their Location_Parent_ID and Location_Root_ID being 0. Locations in green are main regions with their Location_Parent_ID being 1 as they are within the UK. So for example Cambridgeshire is a sub region within the main East Anglia region, hence the Location_Parent_ID for Cambridgeshire is 2 which is also the Location_ID for East Anglia.

    However as you can see Suffolk also comes under East Anglia with its Location_Parent_ID being 2 but it is listed right at the bottom of the list.

    What is the SQL query to order this list so that UK is Listed at the top, then each region in alphabetic order, underneath each region each sub region in alphabetic order, then other main locations in alphabetic order, underneath each main location their respective countries/regions in alphabetic order e.g see below?


    Code:
    UK
    East Anglia
    Cambridgeshire
    Norfolk
    Suffolk
    East Midlands
    Derbyshire
    Lincolnshire
    West Midlands
    Birmingham
    Shropshire
    EU Countries
    France
    Germany	
    Other Countries
    America
    Canada
    Any help would be greatly appreciated.
    Last edited by ozzii; 01-18-09 at 15:28.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you will need as many LEFT OUTER JOINs as there are levels of sub regions from the top

    alternatively, you could completely redesign your table to use the nested set model
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by r937
    you will need as many LEFT OUTER JOINs as there are levels of sub regions from the top

    alternatively, you could completely redesign your table to use the nested set model
    I have tried the following:

    Code:
    SELECT t1.location_name AS lev1,
            t2.location_name as lev2,
            t3.location_name as lev3
    FROM tbl_locations AS t1
            LEFT JOIN tbl_locations AS t2 ON t2.location_parent_id = t1.location_id 
            LEFT JOIN tbl_locations AS t3 ON t3.location_parent_id = t2.location_id 
    WHERE t1.location_parent_id = 0 order by t1.Location_Name,   
              t2.Location_Name, 
              t3.Location_Name ASC;
    But this gives me three columns as shown below and also hierachy for UK is listed at the bottom instead at the top and top level location i.e UK, EU Countries and Other Countries not listed on their own???

    Code:
    lev1		lev2		lev3
    
    EU Countries	France		
    EU Countries	Germany
    Other Countries	America
    Other Countries	Canada
    UK		East Anglia	Cambridgeshire
    UK		East Anglia	Norfolk
    UK		East Anglia	Suffolk
    How do you list them in one column without the sub regions repeating themselves for each new row and with UK at the top?
    Last edited by ozzii; 01-18-09 at 10:37.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in your SELECT clause, use COALESCE to find the first non-NULL location from the bottom of the path going up --
    Code:
    SELECT COALESCE(t3.location_name
                  , t2.location_name
                  , t1.location_name) AS location
    in the ORDER BY, use their sequence columns instead of their names, but keep the three columns

    oh, since you don't have a sequence column, use their id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    oh, wait, that's not quite right, that gives you only the leaf nodes

    you'll need to UNION some INNER JOINs...

    SELECT level1.name FROM level1
    UNION
    SELECT level2.name FROM level1 INNER JOIN level2
    UNION
    SELECT level3.name FROM level1 INNER JOIN level2 INNER JOIN level3

    see the pattern?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by r937
    in your SELECT clause, use COALESCE to find the first non-NULL location from the bottom of the path going up --
    Code:
    SELECT COALESCE(t3.location_name
                  , t2.location_name
                  , t1.location_name) AS location
    in the ORDER BY, use their sequence columns instead of their names, but keep the three columns

    oh, since you don't have a sequence column, use their id
    Ok tried this but as you quite rightly mention in your next post it only gives leaf nodes also ordering by id means it no longer lists locations alphabetically.

  7. #7
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by r937
    oh, wait, that's not quite right, that gives you only the leaf nodes

    you'll need to UNION some INNER JOINs...

    SELECT level1.name FROM level1
    UNION
    SELECT level2.name FROM level1 INNER JOIN level2
    UNION
    SELECT level3.name FROM level1 INNER JOIN level2 INNER JOIN level3

    see the pattern?
    Am completely baffled by this one. Whats full SQL query to implement this in light of the above tried examples?

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What RDBMS are you working with? If it happens to be Microsoft SQL Server 2005 (or above) then I can think of a way to achieve this ordering.
    George
    Home | Blog

  9. #9
    Join Date
    Mar 2007
    Posts
    212
    Hi

    Am using MySQL. If it was Oracle I could have used CONNECT BY PRIOR to achieve this. Am completely stumped by this one! My original query seems almost there but it doesnt list any of the top level locations on their own. What am I missing???

    Code:
    SELECT t1.location_name AS lev1,
            t2.location_name as lev2,
            t3.location_name as lev3
    FROM tbl_locations AS t1
            LEFT JOIN tbl_locations AS t2 ON t2.location_parent_id = t1.location_id 
            LEFT JOIN tbl_locations AS t3 ON t3.location_parent_id = t2.location_id 
    WHERE t1.location_parent_id = 0 order by t1.Location_Name,   
              t2.Location_Name, 
              t3.Location_Name ASC;
    Last edited by ozzii; 01-18-09 at 13:51.

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what order did you want them in?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Mar 2007
    Posts
    212
    I need UK and all it sub regions at the top 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

  12. #12
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by r937
    oh, wait, that's not quite right, that gives you only the leaf nodes

    you'll need to UNION some INNER JOINs...

    SELECT level1.name FROM level1
    UNION
    SELECT level2.name FROM level1 INNER JOIN level2
    UNION
    SELECT level3.name FROM level1 INNER JOIN level2 INNER JOIN level3

    see the pattern?
    I can't for the life of me figure out how the above would achieve the result set in the order that I require. Could you elaborate on the above or am I right in assuming your just as baffelled as me!

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry for not getting back to you sooner on this
    here's the query:
    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 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 two.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
    here are the results:
    Code:
    Location_Name   sortkey1  sortkey2  sortkey3
    UK                 1       NULL      NULL     
    East Anglia        1         2       NULL 
    Cambridgeshire     1         2         3
    Norfolk            1         2         4
    Suffolk            1         2        14
    East Midlands      1         5       NULL  
    Derbyshire         1         5         6
    Lincolnshire       1         5         7
    west Midlands      1        11       NULL 
    Birmingham         1        12       NULL 
    Shropshire         1        13       NULL 
    EU Countries       8       NULL      NULL
    Germany            8         9       NULL 
    France             8        10       NULL
    let me know if you need an explanation

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

  14. #14
    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. If you check your result set you will note under EU countries you have Germany and then France but it should be France and then Germany if its to be ordered alphabetically.

    Thanks for the try though.

Posting Permissions

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