Postgresql - Having trouble performing left joins on multiple tables
postgresql, sql, syntax-error
Solution
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 * FROM
table1
LEFT JOIN table2 ON match1
LEFT JOIN table3 ON match2
WHERE otherFilters
The alternative is the older SQL syntax of:
SELECT cols
FROM table1, table2, table3
WHERE 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_id
left join trip_author ta1 on ta1.publication_id = tp.id
left 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_id
where ta1.order = 1
Problem
I'm in the process of moving some Mysql queries over to Postgresql and I ran across this one that doesn't work. ``` select (tons of stuff) from trip_publication left join trip_collection AS "tc" on tc.id = tp.collection_id left join trip_author ta1, (dies here) trip_person tp1, trip_institution tai1, trip_location tail1, trip_rank tr1 ON tp.id = ta1.publication_id AND tp1.id = ta1.person_id AND ta1.order = 1 AND tai1.id = ta1.institution_id AND tail1.id = tai1.location_id AND ta1.rank_id = tr1.id ``` The query seems to be dying on the "trip_author ta1" line, where I marked it above. The actual error message is: ``` syntax error at or near "," LINE 77: (trip_author ta1, trip_person tp1, ... ``` I went through the docs, and it seems to be correct. What exactly am I doing wrong here? Any feedback would be much appreciated.