SQL - Recursive Query

Question

I have the following mysql table with sample data as follows:

 id    location            parentid
 1     UK                  0
 2     East Anglia         1
 3     Cambridgeshire      2
 4     Norfolk             2
 5     Suffolk             2 
 6     East Midlands       1
 7     Derbyshire          6
 8     Leicestershire      6 
 9     EU Countries        0
 10    Austria             9
 11    Belgium             9

I want to generate a query whereby I can get a list of locations by location name but the location should include any parent location. e.g.

A search for folk should return:

 id  location
 4   Norfolk, East Anglia, UK
 5   Suffolk, East Anglia, UK

A search for East should return:

id  location
2   East Anglia, UK
6   East Midlands, UK

A search for Bel should return:

id  location
11  Belgium 

In the above we are excluding concatanting EU countries

Obviously the following doesnt work:

select c.id, CONCAT_WS(', ', c.location, p.location, pp.location) as location
from tbl_locations c
   outer left join tbl_locations p on p.id = c.parentid
   outer left join tbl_locations pp on pp.id = p.parentid
 where c.location like '%whatever%'

Answer 1

If you only want a the parent location this is done with a self join:

select c.id, c.location, p.id, p.location
from tbl_locations c
  outer left join tbl_locations p on p.id = c.parentid
where c.location like '%whatever%'

This can be extended (via outer joins) to an arbitrary number of levels but the query will get long. Eg. to three levels:

select c.id, c.location, p.id, p.location, pp.id, pp.location
from tbl_locations c
  outer left join tbl_locations p on p.id = c.parentid
  outer left join tbl_locations pp on pp.id = p.parentid
where c.location like '%whatever%'

More general recursive queries depend on the details of the RDBMS. The easiest approach is to use a Common Table Expression (CTE). But MySQL doesn't support them (at least, not yet). Other approaches can be used: Generating Depth based tree from Hierarchical Data in MySQL (no CTEs).

Answer 2

1) There is no standard SQL query which can calculate the "transitive closure" a transitive relation. If you want to nest select statements you will always have a maximum depth that can be reached.

2) There is no standard SQL query which will return a row with a variable number of columns. So you would have to format your results in some way or another (e.g. csv).

However, you can accomplish that in MySQL with a stored procedure:

 1 CREATE DATABASE IF NOT EXISTS test;
 2 USE test;
 3 
 4 
 5 DROP TABLE IF EXISTS location;
 6 CREATE TABLE location (id INT UNSIGNED PRIMARY KEY, name VARCHAR(30) NOT NULL, parent_id INT UNSIGNED NULL REFERENCES location(id));
 7 
 8 INSERT INTO location VALUES
 9 (1,"UK",0), 
10 (2,"East Anglia",1),
11 (3,"Cambridgeshire",2),
12 (4,"Norfolk",2),
13 (5,"Suffolk",2),
14 (6,"East Midlands",1),
15 (7,"Derbyshire",6),
16 (8,"Leicestershire",6);
17 
18 
19 
20 
21 DROP FUNCTION IF EXISTS location_with_parents;
22 DELIMITER //
23 CREATE FUNCTION location_with_parents(location_id INT UNSIGNED) RETURNS VARCHAR(255) READS SQL DATA
24 BEGIN
25     DECLARE LOC_STR VARCHAR(255) DEFAULT NULL;
26     DECLARE LOC_ADD VARCHAR(255) DEFAULT NULL;
27     DECLARE PAR_ID INT UNSIGNED DEFAULT location_id;
28     
29     SELECT name INTO LOC_STR FROM location where id=PAR_ID;
30     loop_label: LOOP 
31         SELECT parent_id INTO PAR_ID FROM location where id=PAR_ID;
32         
33         IF PAR_ID = 0 THEN
34             LEAVE loop_label;
35         ELSE
36             SELECT name INTO LOC_ADD FROM location where id=PAR_ID;
37             SET LOC_STR = CONCAT(LOC_STR, ', ', LOC_ADD); 
38             ITERATE loop_label;
39         END IF;
40     END LOOP loop_label;
41     RETURN LOC_STR;
42     
43 END;
44 //
45 
46 DELIMITER ;
47 
48 
49 
50 SELECT location_with_parents(id) FROM location WHERE name LIKE "%folk%";
51 
52 DROP DATABASE test;

Works for me with MySQL 5.6.35

Hope this helps!

Answer 3

Below Query gives you exact result which you want using Recursion method.

    Select S.ID , 
    concat( S.location,',', Group_concat
    (distinct A.location ORDER BY  A.location  SEPARATOR ',' ) ) as location
    from

         (    SELECT  distinct @r AS _id  ,location,
                        (
                        SELECT  @r := parentid
                        FROM    tbl_locations 
                        WHERE   id = _id
                        ) AS parentid,
                        @l := @l + 1 AS level
                FROM    (
                        SELECT  @r := h.ID,
                                @l := 0,
                                @cl := 0
                         from tbl_locations  h
                         where location like '%folk%'

                        ) vars,
                        tbl_locations  h
                WHERE   @r <> 0


          )A , tbl_locations    S
                where s.location like '%folk%'
                group by S.ID

OutPut :

 location like '%East%' :

location like '%Folk%'

its good question and check and ask if you have any concerns.