Postgres Inner Join Query Three Tables

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

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