How to get an empty result table with FULL JOIN?

Question

I will show you the content of some tables.

Bolek=> SELECT id, description from "TOMBInput";
 id |    description    
----+-------------------
  1 | Virtual Input 111
  2 | Virtual Input 112
  3 | Virtual Input 113
  4 | Virtual Input 114
(4 rows)

Bolek=> SELECT id, setup_id FROM "TRBTOMBConnection";
 id | setup_id 
----+----------
  1 |        1
  2 |        1
  3 |        1
  4 |        1
(4 rows)

Bolek=> SELECT id, setname FROM "Setup";
 id |   setname   
----+-------------
  1 | SETUP_00001
(1 row)

Bolek=> SELECT id, setup_id FROM "Run";
 id | setup_id 
----+----------
  1 |        1
(1 row)

My query [1] is

SELECT
  "TOMBInput".id AS tombinput_id,
  "TRBTOMBConnection".id AS trbtombconnection_id,
  "Setup".id AS setup_id,
  "Run".id AS run_id
FROM "TOMBInput" 
  INNER JOIN "TRBTOMBConnection" ON "TOMBInput".id = "TRBTOMBConnection".tombinput_id
  FULL JOIN "Setup" ON "TRBTOMBConnection".id = "Setup".id
  FULL JOIN "Run" ON "Setup".id = "Run".id AND "Run".id = 1;

Result table

 tombinput_id | trbtombconnection_id | setup_id | run_id 
--------------+----------------------+----------+--------
            1 |                    1 |        1 |      1
            2 |                    2 |          |       
            3 |                    3 |          |       
            4 |                    4 |          |       
(4 rows)

The question is I would like to have table like

 tombinput_id | trbtombconnection_id | setup_id | run_id 
--------------+----------------------+----------+--------
            1 |                    1 |        1 |      1
            2 |                    2 |        1 |      1 
            3 |                    3 |        1 |      1 
            4 |                    4 |        1 |      1 
(4 rows)

because "TRBTOMBConnection" has got 4 rows with setup_id==1 and "Run" has got setup_id==1.

What is more, now when I change last line (in my query [1])

FULL JOIN "Run" ON "Setup".id = "Run".id AND "Run".id = 2;

(in "Run" table we haven`t got id==2) the result of query is

 tombinput_id | trbtombconnection_id | setup_id | run_id 
--------------+----------------------+----------+--------
            1 |                    1 |        1 |       
            2 |                    2 |          |       
            3 |                    3 |          |       
            4 |                    4 |          |       
              |                      |          |      1
(5 rows)

And it`s ok, because I used FULL JOIN.

But in this case when I run my query [1]

I would like to have an empty result table because "Run" hasn't got id==2 and it hasn't got any sense to show table because everything is starting from Run.

How to change my query [1]?

Answer 1

You are confusing IDs:

SELECT
  "TOMBInput".id AS tombinput_id,
  "TRBTOMBConnection".id AS trbtombconnection_id,
  "Setup".id AS setup_id,
  "Run".id AS run_id
FROM "TOMBInput" 
  INNER JOIN "TRBTOMBConnection" ON "TOMBInput".id = "TRBTOMBConnection".tombinput_id
  INNER JOIN "Setup" ON "TRBTOMBConnection".setup_id = "Setup".id
  INNER JOIN "Run" ON "Setup".id = "Run".setup_id AND "Run".id = 1;

I see no reason for full outer joins here.