Recursion - chaining data in sql - stuck

Question

I have different tables and the goal is to obtain the approval workflow for every customer

Customers have different approval workflows, take a look at this:

In my table "entities" i have this

(12, 'Math Andrew', 308, 'CHAIN1-MathAndrew').

It means that when the row was created the number 12 was assigned to Math Andrew... 308 is the number that says that Matt Andrew is a CLIENT

Table type_entities 
(308,'CLIENT'),
(309,'APPROVER1'),
(310,'APPROVER2'),
(311,'APPROVER3'),
(312,'J3 APPROVER4'),
(313,'J4 APPROVER4'),
(314,'J5 APPROVER4'),
(315, 'J6 APPROVER4'),
(316,'J7 APPROVER4');

Because Math Andrew is a CLIENT (also known as CUSTOMER) he must be linked to one or more APPROVERS

A client could have 1 APPROVER, OR 2 APPROVERS OR 3 APPROVERS OR 4 APPROVERS, there exist different approvers inside entities table:

(18, 'ZATCH', 309, null),
(19, 'MAX', 309, null),
(20, 'Ger',310, null),
(21, 'Mar',310, null),
(22, 'Maxwell',311, null),
(23, 'Ryan',312, null),
(24, 'Juy',313, null),
(25, 'Angel',314, null),
(26, 'John',315, null);

Types of relations between entities:

(444,'J6 CLIENT-APPROVER4'),
(445,'J3 CLIENT-APPROVER4'),
(446,'J4 CLIENT-APPROVER4'),
(447,'J10 CLIENT-APPROVER4'),
(448,'J4 CLIENT-APPROVER4'),
(449,'J5 CLIENT-APPROVER4'),
(450,'J10 CLIENT-APPROVER4'),
(451,'J3 CLIENT-APPROVER4'),
(452,'J8 CLIENT-APPROVER4'),
(453,'J5 CLIENT-APPROVER4'),
(454,'J6 CLIENT-APPROVER4'),
(455,'J7 CLIENT-APPROVER4'),
(456,'J7 CLIENT-APPROVER4'),
(457,'J8 CLIENT-APPROVER4'),
(458,'CLIENT-APPROVER3'),
(459,'CLIENT-APPROVER1'),
(460,'APPROVER1-APPROVER2'),
(461,'APPROVER1-APPROVER3'),
(462,'J3 APPROVER1-APPROVER4'),
(463,'APPROVER2-APPROVER3'),
(464,'J3 APPROVER3-APPROVER4'),
(465,'J4 APPROVER3-APPROVER4'),
(466,'J5 APPROVER3-APPROVER4'),
(467,'J6 APPROVER3-APPROVER4'),
(468,'J7 APPROVER3-APPROVER4'),
(469,'J8 APPROVER3-APPROVER4'),
(470,'J10 APPROVER3-APPROVER4'),
(471,'CLIENT-APPROVER2');

This is the important part: when a client is linked to one approver, a relation is created inside relationships table.

In this case MathAndrew was linked to Approver #18 (ZATCH), THIS ROW WAS CREATED AFTER THE ASSIGNATION:

(787,459,'CHAIN1-MathAndrew',18)--

787 IS THE NUMBER THAT WAS ASSIGNED WHEN THAT ROW WAS CREATED 459

REPRESENTS THE RELATION CLIENT - APPROVER

CHAIN1-MathAndre is the

client 18 is the approver

Also, in this case APPROVER1 was linked to APPROVER2

(788,460,18,20)

Then, APPROVER2 was linked to APPROVER3

(789,463,20,21)

Finally, APPROVER3 was linked to APPROVER4

(790,467,21,26)

I WANT TO OBTAIN THE COMPLETE APPROVAL WORKFLOW CHAIN, I mean this: CHAIN1-MathAndrew-ZATCH-Ger-Mar-John

I did this but i am not getting what i want:

WITH relationships_CTE as
select description_entity_1,description_entitiy_2
from relationships
where description_entitiy_1 like 'CHAIN1-MathAndrew'

UNION ALL

select description_entity_1,description_entitiy_2
from relationships
where relationships.description_entitiy_2 = relationships_CTE.description_entitiy_2

select * 
from relationships_CTE ma
left join relationships_CTE na

This is my SQL FIDDLE:

http://sqlfiddle.com/#!9/51bb39/4

Could you please help me?

Answer 1

So you have a couple of major issues with your demo, firstly that you are trying to use a CTE on a version of MySQL that doesn't support it (CTE support was introduced in MySQL version 8), and secondly you are trying to insert a string into a column in the relationships table (which should have been left as a reference to the entities table. Having corrected those issues, we can look at the CTE. There you have a syntax error because you have not enclosed your CTE query in (), and also you have failed to declare the CTE as recursive (since it refers to itself).

Now, based on your question, you want to get names out of the entities table to correspond to the values in the relationships table. So we start the CTE by finding the appropriate entities.id value for CHAIN1-MathAndrew, and then in the recursive part of the CTE we loop through all the entities that are related to that entity, grabbing the names as we go. This gives us this query:

WITH recursive relationships_CTE as (
    select e.id, e.description AS name
    from entities e
    where e.description like 'CHAIN1-MathAndrew'
    UNION ALL
    select r.description_entitiy_2, e.name
    from relationships_CTE cte
    left join relationships r
    on r.description_entitiy_1 = cte.id
    join entities e ON r.description_entitiy_2 = e.id
)

If we now

select *
from relationships_CTE

we get

id  name
12  CHAIN1-MathAndrew
18  ZATCH
20  Ger
21  Mar
26  John

or we can use GROUP_CONCAT to string those names together:

select group_concat(name separator '-')
from relationships_CTE

Output:

CHAIN1-MathAndrew-ZATCH-Ger-Mar-John

Demo on dbfiddle