CODEWITHSUNDEEP
sqlpostgresql
Daniel64
Daniel64

Reputation: 29

Join a table in SQL based off two columns?

I have two tables: 

 Employees (columns: ID, Name)

and 

 employee partners (EmployeeID1, EmployeeID2, Time)

I want to output EmployeName1, EmployeeName2, Time instead of imployee ids.

(In other words, replace the ids with names, but in two columns at a time)

How would I do this? Would JOIN be the appropriate command?

Upvotes: 0

Views: 47

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Often in these situations, you want to use LEFT JOIN:

SELECT e1.name as name1, e2.name as name2, em.time
FROM employee_partners ep LEFT JOIN
     Employees e1
     ON e1.id = ep.EmployeeID1 LEFT JOIN
     Employees e2 
     ON e2.id = ep.EmployeeID2;

Notes:

  • The LEFT JOINs ensure that you do not lose rows if either of the employee columns is NULL.
  • Use tables aliases; they make the query easier to write and to read.
  • Qualify all columns names; that is, include the table name so you know where the column is coming from.
  • I also added column aliases so you can distinguish between the names.

Upvotes: 1

asafm
asafm

Reputation: 921

you need to join the employee table 2 times as the employee partners table acts as many to many connection. The select should be:

SELECT emp1.name, emp2.name, em.time
FROM Employees emp1 
JOIN employee_partners em ON emp1.id = EmployeeID1
JOIN Employees emp2 on emp2.id = EmployeeID2

Upvotes: 1

Related Questions