0
CREATE TABLE `empTest` (
    `keysearch` INT(8) NOT NULL,
    `flg_manager` INT(1) NOT NULL,
    `fk_manager` INT(8) NOT NULL,
    PRIMARY KEY (`keysearch`)
)

INSERT INTO `empTest` (`keysearch`, `fk_manager`, `flg_manager`) VALUES ('5407', '5866', 0);
INSERT INTO `empTest` (`keysearch`, `fk_manager`, `flg_manager`) VALUES ('5866', '0679', 0);
INSERT INTO `empTest` (`keysearch`, `fk_manager`, `flg_manager`) VALUES ('0679', '9177', 1);
INSERT INTO `empTest` (`keysearch`, `fk_manager`, `flg_manager`) VALUES ('9177', '0011', 1);

In the example data above there are 3 users (4th being the last line manager). I would like to be able to select the "1st keysearch where they have the flg_manager set as 1" as AuthManager (indicating they are an Authorising manager)

The query result I am looking to achieve is:

Keysearch,AuthManager

5407,0679

5866,0679

0679,9177

I am thinking it needs to be a loop but I really don't know where to start. I understand I need to use the middle employee as a Join to be able to see from employee 5407 to employee 0679. Annoyingly in this example there is only one employee to 'jump' but I need to be able to account for up to 8 jumps.

Completely Wrong - but I'm at a complete loss...

SELECT e.keysearch, @manager AS manager
FROM emptest e, emptest m1, emptest m2
WHERE @manager = CASE WHEN (
SELECT e.fk_manager
FROM e
WHERE e.fk_manager = m1.keysearch AND m1.flg_manager = 1)

Else When...

Will I need to do lots of Case loops?

Any Suggestions? Running MariaDB 10.3.

D-Shih
  • 44,943
  • 6
  • 31
  • 51
Peter Deacon
  • 1
  • 2
  • You could make [a function and a recursive procedure](https://stackoverflow.com/q/3752078/243245) to find the manager for a given employee ID, but that might be expensive for large numbers of results. If this is something you'll need a lot you could make an employee -> manager map table and add a trigger to emptest that keeps the map table up to date for any changes, but that's not going to be trivial in itself - you might need a third table to store a flat representation of the management hierarchy to spot relevant records to update in the manager table. – Rup Aug 31 '18 at 13:42
  • https://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database The part about adjacency lists might help – OwlsSleeping Aug 31 '18 at 13:53

0 Answers0