I want to edit my query in order to get something a little more ticky.
The goal is to obtain the approval workflow for every customer, displaying that information in this way:
CLIENT | APPROVER1 | APPROVER2 | APPROVER3 | APPROVER4
Previously, i had 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
12 CHAIN1-MathAndre is the client
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 display this in screen:
|CLIENT | APPROVER1 | APPROVER2 | APPROVER3 | APPROVER4|
|CHAIN1-MathAndrew | ZATCH | Ger | Mar | John |
|CHAIN2-JohnConnor | MAX | | Mario | Steven|
|CHAIN3-MarioShapiro | IVAN | | | John |
This is my fiddle:
fiddle
This is my query:
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
NOW, THIS IS WHAT I WANT TO DO:
SUPPOSE that i have a new table called new_table and i modified table entities a little bit:
(12, 'Math Andrew', 308, 45)
(13, 'John Connor', 308, 46)
(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);
table new_table
(45,'Math Andrew', 'Chain1')
(45,'Math Andrew', 'Chain2')
(46, 'John Connor', 'Chain1')
(46, ''John Connor', 'Chain2')
and the table relationships would be like this:
(787,459,'45-Chain1',18)
(788,460,18,20)
(789,463,20,21)
(790,467,21,26)
So, i want to join table entities with table new_table, get the relation 45-Chain1 and then, found 45-Chain1 within table relationships and get something like this (and do the same for all the different clients):
|CLIENT | APPROVER1 | APPROVER2 | APPROVER3 | APPROVER4|
|45-Chain1 | ZATCH | Ger | Mar | John |
I have been trying without success to solve this.
Could you please help me?