Question 292577 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – SQL WHERE clause matching on the same field twice (or more). What is the right JOIN statement to be used?

I have a DB with two tables:

  • employees table: fields = {ID, name}
  • turnShift table: fields = {ID, date, ID_employee1, ID_employee2}

Here is an example of this DB.

enter image description here

Using MYSQL, I’d like to execute a SELECT query able to show the names of both employees involved in each turn shift.

The following picture shows the desired query results using the previous db example:

enter image description here

Tags:

2

Answers


  1. 0

    It can be done as follow:

    SELECT
  t.ID AS turnShiftID,
  t.date AS turnShiftDate,
  e1.name AS employee1Name,
  e2.name AS employee2Name
FROM
  turnShift t
  INNER JOIN employees e1 ON t.ID_employee1 = e1.ID
  INNER JOIN employees e2 ON t.ID_employee2 = e2.ID;
    Login or Signup to reply.
  2. 0

    I’d query the turn_shift table and join on the employee table twice, once for each column of IDs you want to convert to names:

    SELECT t.id, t.date, e1.name AS name_employee1, e2.name AS name_employee2
FROM   turn_shift t
JOIN   employee e1 ON t.id_employee1 = e1.id
JOIN   employee e2 ON t_id_employee2 = e2.id
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search