I have different tables and the goal is to obtain the approval workflow for every customer, displaying that information in this way:
> CLIENT | APPROVER1 | APPROVER2 | APPROVER3 | APPROVER4
First of all, i have a table called entities
(12, 'Math Andrew', 308, 'CHAIN1-MathAndrew') (13, 'John Connor', 308, 'CHAIN2-JohnConnor') (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);
DO NOTE:
12 was assigned to Math Andrew… 308 is the number that says that Matt Andrew is a CLIENT
13 was assigned to John Connor… 308 is the number that says that John Connor is a CLIENT
Because Math Andrew and John Connor are CLIENTS (also known as CUSTOMERS) they 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.
When i say that a client “could have” 1 or more APPROVERS i mean this
CLIENT – APPROVER4 (this is a 1-1 relationship) PS: A CLIENT WILL ALWAYS BE RELATED TO the APPROVER4 IN SOME WAY OR ANOTHER
CLIENT – APPROVER1 – APPROVER4 (in this case there Will be 2 relations.. ONE: CLIENT-APPROVER1 and another APPROVER1-APPROVER4)
CLIENT – APPROVER1 – APPROVER2 – APPROVER4 (in this case there Will be 3 relations.. ONE: CLIENT-APPROVER1, APPROVER1- APPROVER2 AND APPROVER2 – APPROVER4)
AND SO ON… (hopefully you get the idea)
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');
table type_relation
(444,'J6 CLIENT-APPROVER4'), (445,'J3 CLIENT-APPROVER4'), (446,'J4 CLIENT-APPROVER4'), (447,'J10 CLIENT-APPROVER4'), (449,'J5 CLIENT-APPROVER4'), (453,'J5 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');
Types of relations:
CLIENT – APPROVER1 : (459,’CLIENT-APPROVER1′)
CLIENT – APPROVER2 : (471,’CLIENT-APPROVER2′)
CLIENT – APPROVER3 : (461,’APPROVER1-APPROVER3′)
CLIENT – APPROVER4:
(445,’J3 CLIENT-APPROVER4′)
(446,’J4 CLIENT-APPROVER4′)
(449,’J5 CLIENT-APPROVER4′)
(444,’J6 CLIENT-APPROVER4′)
(456,’J7 CLIENT-APPROVER4′)
(457,’J8 CLIENT-APPROVER4′)
(447,’J10 CLIENT-APPROVER4′)
APPROVER 1 -APPROVER 2:
(460,’APPROVER1-APPROVER2′)
APPROVER 2 – APPROVER 3:
(463,’APPROVER2-APPROVER3′)
APPROVER 3 – APPROVER 4:
(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′)
THIS IS IMPORTANT: when a client is linked to one approver, a NEW RELATION is created inside relationships table.
Table relationships:
(787,459,12,18) (788,460,18,20) (789,463,20,21) (790,467,21,26)
787 IS THE NUMBER THAT WAS ASSIGNED WHEN THAT ROW WAS CREATED
459 REPRESENTS THE RELATION: CLIENT – APPROVER
CHAIN1-MathAndre is theclient
18 is the approver
Following the idea:
APPROVER1 was linked to APPROVER2
(788,460,18,20)
APPROVER2 was linked to APPROVER3
(789,463,20,21)
APPROVER3 was linked to APPROVER4
(790,467,21,26)
So, i would like to display this in screen:
|CLIENT | APPROVER1 | APPROVER2 | APPROVER3 | APPROVER4| |CHAIN1-MathAndrew | ZATCH | Ger | Mar | John | |CHAIN2-JohnConnor | MAX | | Mario | Steven| |CHAIN3-MarioShapiro | IVAN | | | John |
The last 2 rows are just an example
This is what i have so far (it IS working):
but it is displaying the information without showing columns name (CLIENT, APPROVER1, APPROVER2, APPROVER3, APPROVER4).. This is displaying this:
CHAIN1-MathAndrew-ZATCH-Ger-Mar-John
I want to display the data in this way:
|CLIENT | APPROVER1 | APPROVER2 | APPROVER3 | APPROVER4| |CHAIN1-MathAndrew | ZATCH | Ger | Mar | John | |CHAIN2-JohnConnor | MAX | | Mario | Steven| |CHAIN3-MarioShapiro | IVAN | | | John |
I am pretty lost, could you please help me?
EDIT:
The maximum amount of approvers is: 4
Advertisement
Answer
You should use conditional aggregation to format your data as you want. Try the following solution, where I assume that you have MySQL ver.8 and the window functions are available:
WITH recursive relationships_CTE as ( select e.id, e.description AS name, 1 col_id, row_number() over (order by e.id) row_id from entities e where e.description like 'CHAIN%' UNION ALL select r.description_entitiy_2, e.name, col_id+ 1, row_id 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 ) select max(case when col_id = 1 then name end) client, max(case when col_id = 2 then name end) approver1, max(case when col_id = 3 then name end) approver2, max(case when col_id = 4 then name end) approver3, max(case when col_id = 5 then name end) approver4 from relationships_CTE group by row_id
The solution uses your SQL query and adds the necessary information for table formating: (1) row_id, and (2) col_id. These values are then used in conditional aggeration to create the table.