sqlmysqlselectwhere-clause

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


Solution

  • 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;