Postgresql - Having trouble performing left joins on multiple tables
I don't know postgres, but in regular SQL you would need to a series of LEFT JOIN
statements rather than your comma syntax. You seemed to have started this then stopped after the first two.
Something like:
SELECT * FROMtable1 LEFT JOIN table2 ON match1LEFT JOIN table3 ON match2WHERE otherFilters
The alternative is the older SQL syntax of:
SELECT colsFROM table1, table2, table3WHERE match AND match2 AND otherFilters
There's a couple of other smaller errors in your SQL, like the fact you forgot your tp
alias on your first table, and have tried including a where
clause (ta1.order = 1
) as a joining constraint.
I think this is what you are after:
select (tons of stuff)from trip_publication tp left join trip_collection AS "tc" on tc.id = tp.collection_idleft join trip_author ta1 on ta1.publication_id = tp.idleft join trip_person tp1 on tp1.id = ta1.person_id left join trip_institution tai1 on tai1.id = ta1.institution_id left join trip_location tail1 on tail1.id = tai1.location_id left join trip_rank tr1 on tr1.id = ta1.rank_idwhere ta1.order = 1
Your left joins are one per table you are joining
left join trip_author ta1 on ....left join trip_person tp1 on ....left join trip_institution on ...
...and so on