CODEWITHSUNDEEP
sqlpostgresql
Denny
Denny

Reputation: 1759

Postgres perform JOIN on a table more than once

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

Upvotes: 0

Views: 2208

Answers (1)

jpw
jpw

Reputation: 44871

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.

Upvotes: 4

Related Questions