SQL Query to order a list

ozzii · #1 (**permalink**) 01-18-09, 07:59

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.

r937 · #2 (**permalink**) 01-18-09, 09:09

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

ozzii · #3 (**permalink**) 01-18-09, 10:32

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?

r937 · #4 (**permalink**) 01-18-09, 10:42

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

r937 · #5 (**permalink**) 01-18-09, 10:46

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?

ozzii · #6 (**permalink**) 01-18-09, 11:43

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.

ozzii · #7 (**permalink**) 01-18-09, 11:46

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?

gvee · #8 (**permalink**) 01-18-09, 13:13

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.

ozzii · #9 (**permalink**) 01-18-09, 13:47

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;

r937 · #10 (**permalink**) 01-18-09, 16:55

what order did you want them in?

ozzii · #11 (**permalink**) 01-20-09, 13:04

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

ozzii · #12 (**permalink**) 01-23-09, 12:35

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!

r937 · #13 (**permalink**) 01-23-09, 14:18

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

ozzii · #14 (**permalink**) 01-23-09, 16:27

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech