Simplify recursive mysql query

Question

With given MySQL table

CREATE TABLE taxons (
    id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    parent_id int,
    name varchar(255)
);

The table has following entries

INSERT INTO taxons (parent_id, name) VALUES (NULL, "Baby & Kleinkind"); 
INSERT INTO taxons (parent_id, name) VALUES (1, "Babysicherheit"); 
INSERT INTO taxons (parent_id, name) VALUES (2, "Babysicherungen & Schutzvorrichtungen"); 

The amount of levels are endless.

I want to select a entity (one taxon) with only one query by traversing the tree down.

Currently my query is kind of handmade

SELECT * 
FROM taxons
WHERE name = "Babysicherungen & Schutzvorrichtungen" 
AND parent_id = (
  SELECT id 
  FROM taxons 
  WHERE name  = "Babysicherheit"
  AND parent_id = (
    SELECT id 
    FROM taxons 
    WHERE name = "Baby & Kleinkind"
  )
);

Are there any better solutions for this query?

See the db fiddle for better understanding: https://www.db-fiddle.com/f/mvMCGdNgjCa9PeNKbbzmRL/0

MySQL v5.7

Answer 1

If I'm reading your query correctly, you want to query the top level records, but only those records where all the child rows exist as well. For example, if "Baby & Kleinkind" doesn't exist for some ID, then we don't want that ID.

If so, you could use dynamic SQL to build a query that automatically checks every parent-child relationship for you. If you have a lot of taxons, this could be save you time.

The idea is to build a query that looks like this (assuming that there are five taxons):

SELECT t1.* 
FROM taxons t1
INNER JOIN taxons t2
  ON t1.id = t2.parent_id 
INNER JOIN taxons t3
  ON t2.id = t3.parent_id
INNER JOIN taxons t4
  ON t3.id = t4.parent_id
INNER JOIN taxons t5
  ON t4.id = t5.parent_id
WHERE NAME = "Babysicherungen & Schutzvorrichtungen" 

To build that, you can use a loop:

DECLARE totalLevels INT;
DECLARE num INT;
DECLARE queryString VARCHAR(255);

SET @totalLevels = 5
SET @num = 2;
SET @str = 'SELECT t1.* 
FROM taxons t1';

build_query: LOOP
  IF @num > @totalLevels THEN
    LEAVE build_query;
  END IF;
  SET @queryString = CONCAT(@queryString,
    ' INNER JOIN taxons t', num, ' ON t', num-1, '.id = t', num, '.parent_id'
  );
  SET @num = @num + 1;
  ITERATE build_query;
END LOOP;

SET @queryString = CONCAT(@queryString, ' WHERE NAME = "Babysicherungen & Schutzvorrichtungen"')

You use totalLevels to set the total number of taxons that you want to traverse.

I haven't specifically tested the code in that loop, but I've done similar queries in the past, and it should work, or only require minor changes to work.

EDIT: I forgot to add, once you create the query string, you need to execute it. For that, see here: Is it possible to execute a string in MySQL?