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?