Creating SQL query

Question

Some of the tables in my database is listed below. enter image description here

route1 moves place1-place2-place1.

route2 moves place1-place2-place3-place2-place1.

I need to create a SQL query that takes 2 places: place1 and place2, and provide routeIDof routes that contain the link of roadways IN ORDER. I believe it requires recursion in the query.

Can anybody help me? I would appreciate very much.

Typically you'd separate places out into it's own table, and join places to roadways, that'd probably help a lot — serakfalcon, Aug 08 '14 at 13:34

score 0 · Answer 1 · answered Aug 08 '14 at 13:53

Well here is a starting point Hierarchical queries in MySQL

A quick reminder: hierarchical data is a parent-child relationship contained in one table.

A typical task is to return values from the table in the following way:

Resultset should be sorted like a tree, that is lexical sort by ancestry chains
Depth level should be returned along with each row

It may sound confusing, but it's very simple in fact, like shown on this Oracle query:

SELECT LPAD(' ', level * 4, ' ') || id, parent, level
FROM t_hierarchy
START WITH parent = 0
CONNECT BY parent = PRIOR id

We have a nice tree sorted as a tree, with rows indented according to the depth level.

In the query above, START WITH defines the root of the tree, and CONNECT BY defines join condition between parent and child rows. Parent columns are defined by adding PRIOR keyword to them.

In MySQL there is no such construct, but it can be emulated.

CREATE FUNCTION hierarchy_connect_by_parent_eq_prior_id(value INT) RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
        DECLARE _id INT;
        DECLARE _parent INT;
        DECLARE _next INT;
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;

        SET _parent = @id;
        SET _id = -1;

        IF @id IS NULL THEN
                RETURN NULL;
        END IF;

        LOOP
                SELECT  MIN(id)
                INTO    @id
                FROM    t_hierarchy
                WHERE   parent = _parent
                        AND id > _id;
                IF @id IS NOT NULL OR _parent = @start_with THEN
                        SET @level = @level + 1;
                        RETURN @id;
                END IF;
                SET @level := @level - 1;
                SELECT  id, parent
                INTO    _id, _parent
                FROM    t_hierarchy
                WHERE   id = _parent;
        END LOOP;       
END

score 0 · Answer 2 · edited May 23 '17 at 12:30

There are several questions on this topic already present in Stack Overflow:

Generating Depth based tree from Hierarchical Data in MySQL (no CTEs) Generating Depth based tree from Hierarchical Data in MySQL (no CTEs)

MySQL recursive subquery [duplicate] MySQL recursive subquery

What are the Options for Storing Hierarchical Data in a Relational Database? What are the options for storing hierarchical data in a relational database?

How to query graph/hierarchical data in mysql How to query graph/hierarchical data in mysql

Hierarchical Data in MySql Hierarchical Data in MySql

Creating SQL query

2 Answers2