How to dynamically add INNER JOIN in a MySql Query?

Question

I am working on a personal project and I don't know how to create a dynamic query with MySQL. I would like to get all version of a file stored in a database. For this purpose I replaced file by simplest name "first" in a test database created with this script:

/*
DROP DATABASE IF EXISTS request_test;
CREATE DATABASE IF NOT EXISTS request_test 
     DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE request_test;
*/

DROP TABLE IF EXISTS first_next;
CREATE TABLE first_next(
    id INT(3) UNSIGNED ,
    first VARCHAR(40) NOT NULL,
    ik_next INT(3) NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE first_next ADD PRIMARY KEY (`id`);
ALTER TABLE first_next MODIFY `id` INT(3) NOT NULL AUTO_INCREMENT;
ALTER TABLE first_next ADD CONSTRAINT fn1 FOREIGN KEY (ik_next) REFERENCES first_next(id);

Sample data:

 INSERT INTO first_next (id, first, ik_next)
 VALUES
 (1, 'toto1', NULL),
 (2, 'titi1', NULL),
 (3, 'riri1', NULL),
 (4, 'titi2', 2),
 (5, 'fifi1', NULL),
 (6, 'toto2', 1),
 (7, 'titi3', 4),
 (8, 'fifi2', 5),
 (9, 'fifi3', 8),
 (10, 'toto3', 6),
 (11, 'loulou1', NULL),
 (12, 'toto4', 10);

As you can see toto1 is replaced by successively 3 other name toto2, toto3 and toto4. With the following query I get the full history of toto1:

SELECT first.id,first.first AS initiale, 
       second.id,second.first AS suivante_1,
       third.id,third.first AS suivante_2,
      fourth.id,fourth.first AS suivante_3
  FROM first_next AS first
  INNER JOIN first_next AS second 
          ON second.ik_next = first.id
  INNER JOIN first_next AS third
          ON third.ik_next = second.id
  INNER JOIN first_next AS fourth
          ON fourth.ik_next = third.id
WHERE fourth.id = 12
ORDER BY first.id ASC;

In this case I added manually 3 "INNER JOIN" and I would like to dynamically adapt query with history of name. Imagine if toto5, toto6 exists. This is where I am stuck and I don't know how to solve my problem.

What you are probably thinking of is to use a [left join](https://stackoverflow.com/q/5706437) instead of join (you could e.g. add 10 left joins "just in case", but *need* to know the maximum depth of your hierarchy). Nevertheless, what you probably *actually* want to know you may find at [How to create a MySQL hierarchical recursive query?](https://stackoverflow.com/q/20215744). Note: if you are using MySQL 8, the queries get a lot simpler, so try them (and/or ask about them), even if recursive ctes look complicated at the first try. — Solarflare, Nov 03 '21 at 00:54

How to dynamically add INNER JOIN in a MySql Query?

0 Answers0