Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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, '%')));
Add Comment
Please, Sign In to add comment