Obtem a árvore pai de um resultado de pesquisa

tree_result.sql

drop table tree;

create table tree (
  id INT,
  path VARCHAR(255),
  name VARCHAR(100)
);

insert into tree VALUES (1, '/', 'Prédio Orozimbo');
insert into tree VALUES (2, '/1', 'Ala a');
insert into tree VALUES (3, '/1', 'Ala b');
insert into tree VALUES (4, '/1/2', 'RH');
insert into tree VALUES (5, '/1/2', 'ADM');
insert into tree VALUES (6, '/1/3', 'Tecnico');
insert into tree VALUES (7, '/1/3', 'Visitante');
insert into tree VALUES (8, '/1/3/7', 'sala b');
insert into tree VALUES (9, '/1/3/7/8', 'sala d');
insert into tree VALUES (10, '/1/2/5', 'sala a');
insert into tree VALUES (11, '/1/2/5', 'sala c');

// Exemplo pesquisa por nome
SELECT DISTINCT parent.id, parent.path, parent.name
  FROM tree AS leaf
    JOIN tree AS parent
WHERE leaf.name LIKE '%sala%'
  AND ((leaf.name = parent.name AND leaf.path = parent.path)
OR (leaf.path LIKE CONCAT('%', parent.id, '%')));

// Exemplo pesquisa por nome e tags
SELECT DISTINCT parent.id, parent.path, parent.name
FROM tree AS leaf
  JOIN tree AS parent
WHERE (leaf.name LIKE '%visita%') AND (leaf.tags LIKE '%emuso%' OR leaf.tags LIKE '%virtual%')
      AND ((leaf.name = parent.name AND leaf.path = parent.path)
           OR (leaf.path LIKE CONCAT('%', parent.id, '%')));