MariaDB SQL Recursive call to self (single table)

If you need to recursively query data based on child / parent relation between rows, you can achieve that using WITH RECURSIVE & UNITE ALL query instructions.

Let’s have a look at example “software table”. Software can be declared as virtual machine, virtualizer, operating system or simple program running on the OS. We connect them based on parent relation.

SQL Table schema

Table Data – Software (partial example data)

SQL

SELECT
  s1.id, s1.name software, s2.id parent_id, s2.name parent_name
FROM
  software s1
LEFT JOIN
  software s2
ON  s2.id = s1.parent_id

So for example, Adobe runs on Windows OS which runs on Virtual machine which runs on virtualiser lets say KVM. If you want to get all parents starting from Adobe to the KVM, you would consider the fallowing SQL query:

SQL Query

WITH RECURSIVE virtable AS (
	SELECT * FROM software s1 WHERE NAME LIKE "%E2%"
	UNION ALL
	SELECT s2.*
	FROM software AS s2, virtable AS v
	WHERE
	s2.id = v.parent_id
)
SELECT id, name, parent_id, software_type FROM virtable

Query result

software

generated 2024-03-13 10:41:12 by HeidiSQL 12.6.0.6