I have a table called run:

I have two mapping tables:

I want to join them to create a table that looks like this:

Does anyone know how I can achieve this? Thanks in advance! I was thinking of an inner join but on Scenario and System but I did not know how to reconcile with the second mapping table having project rather than scenario. I would really appreciate some guidance!

CodePudding user response：

I don't really see where the problem lies.

The runs seem to be runs of one scenario, on a certain date, and a certain system, and a result "pass" or "fail".

The first intersection table, which I called pj2scen maps a scenario to a project that it belongs to.

The second seems to be rather a lookup table, giving us the priority that a certain system obtains within a certain project.

So it becomes obvious to obtain the project from the pj2scen table, by joining using the scenario from the run table, then to join with the prio table using both the project obtained, and the system from the run table. What else, I ask myself?

btw, I always rename columns/tables with reserved words as names, like date, system or priority

WITH
-- your input - don't use in the final query
run(Scenario,Dt,Sys,Result) AS (
          SELECT 'Scen1',DATE '2022-07-01','A','PASS'
UNION ALL SELECT 'Scen1',DATE '2022-07-01','B','PASS'
UNION ALL SELECT 'Scen1',DATE '2022-07-01','C','PASS'
UNION ALL SELECT 'Scen1',DATE '2022-07-01','D','PASS'
UNION ALL SELECT 'Scen1',DATE '2022-07-02','A','FAIL'
UNION ALL SELECT 'Scen1',DATE '2022-07-02','B','FAIL'
UNION ALL SELECT 'Scen1',DATE '2022-07-02','C','FAIL'
UNION ALL SELECT 'Scen1',DATE '2022-07-02','D','FAIL'
)
,
pj2scen (Project,Scenario) AS (
          SELECT 'Proj1','Scen1'
)
,
prio(Project,Sys,Prio) AS (
          SELECT 'Proj1','A',2
UNION ALL SELECT 'Proj1','B',3
UNION ALL SELECT 'Proj1','C',1
UNION ALL SELECT 'Proj1','D',4
)
-- end of your input, real query starts here ...
SELECT
  run.*
, prio.prio
FROM run 
JOIN pj2scen USING(scenario)
JOIN prio USING(project,sys)
ORDER BY result DESC, sys; 
-- out  Scenario |     Dt     | Sys | Result | prio 
-- out ---------- ------------ ----- -------- ------
-- out  Scen1    | 2022-07-01 | A   | PASS   |    2
-- out  Scen1    | 2022-07-01 | B   | PASS   |    3
-- out  Scen1    | 2022-07-01 | C   | PASS   |    1
-- out  Scen1    | 2022-07-01 | D   | PASS   |    4
-- out  Scen1    | 2022-07-02 | A   | FAIL   |    2
-- out  Scen1    | 2022-07-02 | B   | FAIL   |    3
-- out  Scen1    | 2022-07-02 | C   | FAIL   |    1
-- out  Scen1    | 2022-07-02 | D   | FAIL   |    4

CodePudding user response：

The query should be like this:

with main as (
select 'Scen1' scenario, '07-01' datex, 'A' system,  'PASS' result union
select 'Scen1' scenario, '07-01' datex, 'B' system,  'PASS' result union
select 'Scen1' scenario, '07-01' datex, 'C' system,  'PASS' result union
select 'Scen1' scenario, '07-01' datex, 'D' system,  'PASS' result union
select 'Scen1' scenario, '07-02' datex, 'A' system,  'FAIL' result union
select 'Scen1' scenario, '07-02' datex, 'B' system,  'FAIL' result union
select 'Scen1' scenario, '07-02' datex, 'C' system,  'FAIL' result union
select 'Scen1' scenario, '07-02' datex, 'D' system,  'FAIL' result 
), project as(
select 'Proj1' project, 'Scen1' scenario
), priority as (
select 'Proj1' project, 'A' system, '2' score union
select 'Proj1' project, 'B' system, '3' score union
select 'Proj1' project, 'C' system, '1' score union
select 'Proj1' project, 'D' system, '4' score 
)select m.scenario, m.datex, m.system, m.result, o.score from main m
join project p on m.scenario = p.scenario
join priority o on o.project = p.project
and o.system = m.system
order by 1,2,3
;

Fiddle