SELECT data when id in WHERE clause is from previous row

Question

I have 2 tables. The first holds id of last operation, the second - ids of operation and some data

mysql> select * from t1;
+----+--------------+
| id | id_last_oper |
+----+--------------+
|  1 |           45 |
+----+--------------+

mysql> select * from t2;
+-------+------------+---------+
| fo_id | fo_prev_id | fo_name |
+-------+------------+---------+
|     1 |       NULL | a       |
|     5 |          1 | a       |
|    22 |          5 | a       |
|    45 |         22 | a       |
+-------+------------+---------+

Here is my query:

select fo_id from t2 
           where fo_id=44 and @id:=fo_prev_id 
    union select fo_id from t2 where fo_id=@id
    union ...;

I need select all rows, but number of rows is non-permanent and I am unable to select all rows with simple UNION. How can I achieve my goal?

Hey, do not downwote me. I did not know about recursive hierarchical query. — vit, Dec 23 '16 at 07:25
@vit DESIRE RESULT? Not understand what you want to achieve. — Munavvar, Dec 23 '16 at 08:47
Too bad MySQL doesn't have windowing functions. If you are running MariaDB, check into them at https://mariadb.com/kb/en/mariadb/window-functions-overview/ — Spechal, Dec 23 '16 at 08:55

score 3 · Answer 1 · edited May 23 '17 at 12:16

I had the same problem a few months ago. There are two Solutions.

Solution 1 (recommended) - Work with "Left/Right-Relations"
A relational database is not designed for hierarchical structure. This linked helped me, to change the structure of the table for using hierarchical structure. It ist fast and easy for use: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/
I recommend you to use this solution, because it is independent of the database you use and my experiences are consistently positive

Solution 2 - Stored Procedure
In this case you can call your stored procedure itself recursively. In this statement it is good explained. If you scroll more down, so see the same link in solution 1.
mysql stored procedure that calls itself recursively

score 0 · Answer 2 · answered Dec 23 '16 at 09:06

i am not really sure if that is what you looking for:

SELECT result.*
FROM (
    SELECT
        (select fo_id FROM t2 where fo_id = @xid ) as ids
        , @xid := (select `fo_prev_id` FROM t2 where fo_id = @xid ) as next_id
        , @row := @row +1 as row_order
    FROM t2 AS dummy
    CROSS JOIN (SELECT @xid := 45, @row := 0) as init
) AS orderd_ids
LEFT JOIN t2 AS result ON result.`fo_id` = orderd_ids.ids
ORDER BY orderd_ids.row_order;

sample

mysql> SELECT * FROM t2;                                                                                                                                                                                 +-------+------------+---------+
| fo_id | fo_prev_id | fo_name |
+-------+------------+---------+
|     1 |       NULL | a       |
|     5 |          1 | a       |
|    22 |          5 | a       |
|    45 |         22 | a       |
+-------+------------+---------+
4 rows in set (0,00 sec)

mysql> SELECT result.*
    -> FROM (
    ->     SELECT
    ->          (select fo_id FROM t2 where fo_id = @xid ) as ids
    ->          , @xid := (select `fo_prev_id` FROM t2 where fo_id = @xid ) as next_id
    ->          , @row := @row +1 as row_order
    ->      FROM t2 AS dummy
    ->      CROSS JOIN (SELECT @xid := 45, @row := 0) as init
    -> ) AS orderd_ids
    -> LEFT JOIN t2 AS result ON result.`fo_id` = orderd_ids.ids
    -> ORDER BY orderd_ids.row_order;
+-------+------------+---------+
| fo_id | fo_prev_id | fo_name |
+-------+------------+---------+
|    45 |         22 | a       |
|    22 |          5 | a       |
|     5 |          1 | a       |
|     1 |       NULL | a       |
+-------+------------+---------+
4 rows in set (0,00 sec)

mysql>

sample 2

mysql> SELECT * FROM t2;
+-------+------------+---------+
| fo_id | fo_prev_id | fo_name |
+-------+------------+---------+
|     1 |         66 | a       |
|     5 |          1 | a       |
|    22 |          5 | a       |
|    45 |         22 | a       |
|    66 |         72 | a       |
|    72 |       NULL | a       |
+-------+------------+---------+
6 rows in set (0,00 sec)

mysql> SELECT result.*
    -> FROM (
    ->     SELECT
    ->          (select fo_id FROM t2 where fo_id = @xid ) as ids
    ->          , @xid := (select `fo_prev_id` FROM t2 where fo_id = @xid ) as next_id
    ->          , @row := @row +1 as row_order
    ->      FROM t2 AS dummy
    ->      CROSS JOIN (SELECT @xid := 45, @row := 0) as init
    -> ) AS orderd_ids
    -> LEFT JOIN t2 AS result ON result.`fo_id` = orderd_ids.ids
    -> ORDER BY orderd_ids.row_order;
+-------+------------+---------+
| fo_id | fo_prev_id | fo_name |
+-------+------------+---------+
|    45 |         22 | a       |
|    22 |          5 | a       |
|     5 |          1 | a       |
|     1 |         66 | a       |
|    66 |         72 | a       |
|    72 |       NULL | a       |
+-------+------------+---------+
6 rows in set (0,00 sec)

mysql>

SELECT data when id in WHERE clause is from previous row

2 Answers2