Joining a single column into two separate columns

Question

I have a person table that stores the names of people:

personid    firstname   lastname
526         Joe         Burnaby
527         Andy        Brandt
528         Ben         Oxfold

These persons are stored in customer and employee tables

customerid  personid
745         526
746         527

employeeid  personid
628         528

Then there is a training table that stores each column as so

trainingid  trainerid   customerid  rating
900         628         745         4
901         628         751         2

How can I write a Select that displays both names? The expected output should be

Trainer     Customer    Rating
Ben Oxfold  Joe Burnaby 4
Ben Oxfold  Andy Brandt 2

I'm not sure how to go about writing a Select for this, as I bump into a wall in the columns I select:

SELECT 
person.firstname||person.lastname AS "Trainer",
person.firstname||person.lastname AS "Customer",
rating
FROM training
JOIN employee ON training.trainerid=employee.employeeid
JOIN customer ON training.customerid=customer.customerid
JOIN person ON employee.personid=person.personid
JOIN person ON customer.personid=person.personid;

I tried this, but obviously it will only display whoever you join first in both rows.

Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... — , Dec 10 '18 at 07:18
Customer 751 in Training table is not listed in Customer table. — donPablo, Dec 10 '18 at 07:50

ScaisEdge · Accepted Answer · 2018-12-10T07:35:14.887

You should join the person table two times using alias for the right join clause

SELECT   p1.firstname||' '||p1.lastname AS Trainer
       , p2.firstname||' '||p2.lastname AS Customer
       , t.rating
FROM training t 
INNER JOIN  employee e ON e.employeeid = t.trainerid 
INNER JOIN customer c ON c.customerid = t.customerid 
INNER JOIN person p1 ON p1.personid = e.personid 
INNER JOIN person p2 ON p2.personid = c.personid

Joining a single column into two separate columns

1 Answers1