CODEWITHSUNDEEP
sqlpostgresqlinner-joinjoin
andrew
andrew

Reputation: 3141

Use two JOINs on the same table in sql query

I have a query like below which is working fine.

SELECT 
    "A".id AS A_id,
    "B".id AS B_id,
    "C".id AS C_id,
    "D".id AS D_id,
    "E".id AS E_id,
    "F".id AS F_id,
    "G".id AS G_id,
    "H".id AS H_id,
    "I".id AS I_id
FROM "A" 
INNER JOIN "B" ON "A".id = "B".A_id 
INNER JOIN "C" ON "B".C_id = "C".id
INNER JOIN "D" ON "C".D_id = "D".id
INNER JOIN "E" ON "D".id = "E".D_id
INNER JOIN "F" ON "E".F_id = "F".id
INNER JOIN "G" ON "F".G_id = "G".id
INNER JOIN "H" ON "G".id = "H".G_id
INNER JOIN "I" ON "H".I_id = "I".id;

Now I would like to add special condition. Table "B" is connected with another table and I have to add special join (full join) but after all joins. Something like below (it`s not working of course):

SELECT 
    "A".id AS A_id,
    "B".id AS B_id,
    "C".id AS C_id,
    "D".id AS D_id,
    "E".id AS E_id,
    "F".id AS F_id,
    "G".id AS G_id,
    "H".id AS H_id,
    "I".id AS I_id,
    "Setup".id AS setup_id,
    "Run".id AS run_id
FROM "A" 
INNER JOIN "B" ON "A".id = "B".A_id 
INNER JOIN "C" ON "B".C_id = "C".id
INNER JOIN "D" ON "C".D_id = "D".id
INNER JOIN "E" ON "D".id = "E".D_id
INNER JOIN "F" ON "E".F_id = "F".id
INNER JOIN "G" ON "F".G_id = "G".id
INNER JOIN "H" ON "G".id = "H".G_id
INNER JOIN "I" ON "H".I_id = "I".id

AND FROM
"Setup" 
FULL JOIN "Run" ON "Setup".id = "Run".id
FULL JOIN "B" ON "Setup".id = "B".id;

I used full join because each of the table "Run" and "Setup" include only one row but are connected with table "B" which has got more that one row and I want to have all of them in the result table.

How to repair it? Maybe try to use select in select?

Upvotes: 0

Views: 990

Answers (1)

Karl Kieninger
Karl Kieninger

Reputation: 9129

You don't need a full join to get multiple rows reflected in the 1 to many relationships you describe. another inner join. And there is no "AND FROM" construction. You can simple add the joins directly.

INNER JOIN "I" ON "H".I_id = "I".id
INNER JOIN "Setup" ON "B".id = "Setup".id;
INNER JOIN "Run" ON "Setup".id = "Run".id

You should probably read-up on joins.

[EDIT]

Comment indicate the data is asymmetric such that you do need an outer join, so:

INNER JOIN "I" ON "H".I_id = "I".id
LEFT JOIN "Setup" ON "B".id = "Setup".id;
LEFT JOIN "Run" ON "Setup".id = "Run".id

Upvotes: 2

Related Questions