Postgres perform JOIN on a table more than once

Question

I am looking for a way to join a table more than once...say I got a table with 3 columns and 2 of these columns are foreign keys referencing other tables e.g I got table, say customers with fields id, firstname, lastname. I then have a second table, say cars with fields id, reg_no, owner ...lastly there is the 3rd table 'assignments' now with foreign keys, as such - id, driver_assigned (FK referencing customers), who_assigned (FK referencing customers), car (FK referencing cars). What SQL syntax can I use to join? At the moment I use the query ...

SELECT a.firstname || ' ' || a.middlename AS Conductor, b.date_added AS Date_Assigned, a.firstname || ' ' || a.middlename AS Assigned_By FROM customers a JOIN ndovu_matatu_conductors b ON a.customer_id=b.customer_id

and I get the data below but now the conductor and assigned_by columns shouldn't have the same values...

conductor date_assigned assigned_by Dennis 2014-09-24 Dennis Dennis 2014-09-24 Dennis Davies 2014-09-24 Davies Dennis 2014-09-25 Dennis Jairus 2014-09-26 Jairus Jairus 2014-09-26 Jairus

It should be a very straight forward SELECT ... FROM x JOIN y ON ... JOIN z ON .... Could you add your attempts to be able to debug what's wrong with the queries? — Joachim Isaksson
– Joachim Isaksson, Commented Sep 26, 2014 at 10:49
Can you share some sample data and the result you want to achieve? — Mureinik
– Mureinik, Commented Sep 26, 2014 at 10:51
I just edited my question with the query I use and the data I get — Denny
– Denny, Commented Sep 26, 2014 at 11:04
Well your query refers to the same firstname and middlename twice. — yamny
– yamny, Commented Sep 26, 2014 at 11:32

jpw · Accepted Answer · 2014-09-26 11:22:21Z

Your description of the tables doesn't match the tables used in the query, but in any case what you want is to join the customers table twice, one time for the driver_assigned and one time for who_assigned. (Or whatever the correct columns are.)

Something like this (based on the table descriptions):

select c1.firstname || ' ' || c1.middlename AS Driver_assigned, c2.firstname || ' ' || c2.middlename AS Who_assigned, from assignments a join customers c1 on a.driver_assigned=c1.customer_id join customers c2 on a.who_assigned=c2.customer_id

I hope you get the concept, even though the columns/tables might be wrong.

Collectives™ on Stack Overflow

Postgres perform JOIN on a table more than once

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related