I have a table table_staff

I need to give staff_id and take all of her parent_staff, please help, thanks in advance

Closed due to the fact that the essence of the question is unclear by the participants pavel , Vadim Ovchinnikov , Kromster , user194374, Yuri 12 Jan '17 at 18:13 .

Try to write more detailed questions. To get an answer, explain what exactly you see the problem, how to reproduce it, what you want to get as a result, etc. Give an example that clearly demonstrates the problem. If the question can be reformulated according to the rules set out in the certificate , edit it .

  • I need to give staff_id and take all of her parent_staff - explain it here. Who to give? And How? what does "take" mean? - Akina
  • @Akina seems to file the staff_id at the entrance, find the parent_staff (output) - then find its parent_staff (output), etc - SLy_huh
  • @SLy_huh I would like to see all this (or not this, but something else) by the author. And in the text of the question. - Akina
  • @Akina, IMHO, the question of recursion in the case, no matter how clumsy it is asked. I would like to see the opinion of the Guru from SQL, what to do in such cases. - Alexander Muksimov
  • one
    Yes, I need to give the input staff_id, find parent_staff (output) - then find its parent_staff (output), etc. ... - Tiko

3 answers 3

Since we need to move through the tree from the descendant to the parent, at each level of the hierarchy we have only one entry added. So we don’t need recursion at all, but we need a cycle that the parent of the current record will receive at each step.

There are no cycles in SQL, so we will emulate it using an arbitrary table (or query) that will produce enough rows to get all the parents. In this example, I decided to use the unique parent_staff from the table as such a cursor, since there are certainly no fewer of them than the iterations of the “loop” need. If the maximum possible hierarchy depth is known it is better (for speed) to limit its limit.

select distinct id from ( select @id:=(select parent_staff from staff where staff_id=@id) id from (select distinct parent_staff from staff) as curs, (select @id:=2 /* стартовый staff_id */ ) A ) B
  • Thanks turned out - Tiko
  • Mike where say it I'll do! green check mark - Tiko
 with recursive tree (TABLE, staff_id, level, pathstr) as (select TABLE, staff_id, 0, cast('' as text) from tree_sample where staff_parent_staff is null union all select tree_sample.TABLE, tree_sample.staff_id, t.level + 1, tree.pathstr + tree_sample.TABLE from tree_sample inner join tree on tree.staff_id = tree_sample.staff_parent_staff) select staff_id, space( level ) + TABLE as TABLE from tree order by pathstr
  • And where is the required recursion for staff_id about all parents? - Alexander Muksimov
  • it’s not written about the question in question - Paulo Berezini
  • Wrong, written. Implicitly in the text ( and take all of her parent_staff ) and explicitly in the title. - Akina
  • @Pavel Bereznichenko, wasn’t it in the headline? - Alexander Muksimov
  • Aha right away did not find all the hidden meanings, corrected) - Paulo Berezini

MySQL does not yet have recursive requests to stable, so only HP.

On the other hand, recursion is not needed here, from the word "absolutely." If schematically, it will be somewhere like this:

 CREATE PROCEDURE get_all_parents(IN child_id BIGINT) BEGIN DECLARE cnt BIGINT DEFAULT 0; DROP TEMPORARY TABLE IF EXISTS tmp_parents; CREATE TEMPORARY TABLE tmp_parents(id BIGINT UNIQUE) ENGINE=Memory; INSERT INTO tmp_parents(id) SELECT child_id; WHILE (SELECT COUNT(*) FROM tmp_parents) > cnt SELECT COUNT(*) INTO cnt FROM tmp_parents; INSERT IGNORE INTO tmp_parents(id) SELECT parent_id FROM datatable WHERE id IN ( SELECT id FROM tmp_parents ); END WHILE; /* DELETE FROM tmp_parents WHERE id = child_id; */ SELECT id FROM tmp_parents; DROP TEMPORARY TABLE tmp_parents; END;