Select statement from 3 tables with join How can I select from 3 tables (task, status, employee)?

Task

TASK_ID (PK), 
TASK_NAME ,
DESCRIPTION ,
STATUS_CODE (FK for status table) 
START_DATE ,
END_DATE ,
TASK_TYPE_CODE (FK) ,
PRIORITY_CODE(FK) ,
EMPLOYEE_ID (FK for employee table) 


Status

STATUS_CODE (PK) ,
STATUS_NAME 


Employee

EMPLOYEE_ID (PK), 
EMPLOYEE_NAME 


I want to select task_id, task_name, and end_date "from-to" (any date from the calendar) to (any end date) from the task table, status_ name from the status table, and employee_name from the employee table.
To answer this question, I did the following:

I created three tables - Status, Employee and Task (scripts at bottom of post):

This works for MySQL - other servers should be very similar - apart from the DATEDIFF() function - that will depend on your RDBMS.

Status:

mysql&gt; SELECT * FROM Status;
+-----------+-------------+
| Status_ID | Status_Name |
+-----------+-------------+
|         1 | Status1     |
|         2 | Status2     |
|         3 | Status3     |
|         4 | Status4     |
+-----------+-------------+


Employee:

mysql&gt; SELECT * FROM Employee;
+-------------+---------------+
| Employee_ID | Employee_Name |
+-------------+---------------+
|           1 | Billy Joe     |
|           2 | Mary Lou      |
|           3 | PJ Mara       |
+-------------+---------------+


and Task:

mysql&gt; SELECT * FROM Task;
+---------+-----------+-------------+----------+------------+------------+
| Task_ID | Task_Name | Task_Status | Task_Emp | Start_Date | End_Date   |
+---------+-----------+-------------+----------+------------+------------+
|       1 | Task_1    |           2 |        3 | 2016-03-17 | 2016-04-17 |
|       2 | Task_2    |           3 |        1 | 2016-03-10 | 2016-07-15 |
|       3 | Task_3    |           4 |        2 | 2016-04-13 | 2016-05-17 |
|       6 | Task_6    |           2 |        1 | 2016-01-17 | 2016-08-05 |
|       7 | Task_7    |           3 |        2 | 2016-02-17 | 2016-06-09 |
|       8 | Task_8    |           4 |        3 | 2016-04-17 | 2016-06-10 |
|      10 | Task_10   |           1 |        1 | 2016-03-17 | 2016-08-17 |
|      11 | Task_4    |           1 |        3 | 2016-02-17 | 2016-06-16 |
|      12 | Task_5    |           1 |        1 | 2016-04-17 | 2016-07-20 |
|      14 | Task_9    |           2 |        2 | 2016-04-17 | 2016-05-12 |
+---------+-----------+-------------+----------+------------+------------+


Then, to amalgamate that information in a more human readable format, you can use the following query:

SELECT 
  t.Task_ID, 
  t.Task_Name,
  DATEDIFF(t.End_Date, t.Start_Date) AS Total_Days, 
  CASE 
    WHEN End_Date &lt;= CURDATE() THEN 0
    ELSE DATEDIFF(t.End_Date, GREATEST(t.Start_Date, CURDATE()))
  END AS Days_Remaining,
  t.End_Date, 
  s.Status_Name, 
  e.Employee_Name
FROM Task t
JOIN Employee e ON t.Task_Emp = e.Employee_ID
JOIN Status   s ON t.Task_Status = s.Status_ID
ORDER BY Task_Name;



The CASE statement gives Days_Remaining as 0 if the End_Date is &lt;= today.
If the Start_Date is in the future, it gives the End_Date - the Start_Date
If today's date is between the Start_Date and the End_Date, it gives the End_Date - today's date (since days in the past cannot be remaining).


Which gives the following result:

+---------+-----------+------------+----------------+------------+-------------+---------------+
| Task_ID | Task_Name | Total_Days | Days_Remaining | End_Date   | Status_Name | Employee_Name |
+---------+-----------+------------+----------------+------------+-------------+---------------+
|       1 | Task_1    |         31 |              0 | 2016-04-17 | Status2     | PJ Mara       |
|      10 | Task_10   |        153 |            122 | 2016-08-17 | Status1     | Billy Joe     |
|      16 | Task_11   |         58 |              0 | 2016-02-28 | Status1     | Billy Joe     |
|       2 | Task_2    |        127 |             89 | 2016-07-15 | Status3     | Billy Joe     |
|       3 | Task_3    |         34 |             30 | 2016-05-17 | Status4     | Mary Lou      |
|      11 | Task_4    |        120 |             60 | 2016-06-16 | Status1     | PJ Mara       |
|      12 | Task_5    |         94 |             94 | 2016-07-20 | Status1     | Billy Joe     |
|       6 | Task_6    |        201 |            110 | 2016-08-05 | Status2     | Billy Joe     |
|       7 | Task_7    |        113 |             53 | 2016-06-09 | Status3     | Mary Lou      |
|       8 | Task_8    |         54 |             54 | 2016-06-10 | Status4     | PJ Mara       |
|      14 | Task_9    |         25 |             25 | 2016-05-12 | Status2     | Mary Lou      |
+---------+-----------+------------+----------------+------------+-------------+---------------+


Database scripts:

mysql&gt; CREATE TABLE Status(Status_ID INT AUTO_INCREMENT PRIMARY KEY, Status_Name VARCHAR(10));
mysql&gt; INSERT INTO Status (Status_Name) VALUES('Status1');
mysql&gt; INSERT INTO Status (Status_Name) VALUES('Status2');
mysql&gt; INSERT INTO Status (Status_Name) VALUES('Status3');
mysql&gt; INSERT INTO Status (Status_Name) VALUES('Status4');
Query OK, 1 row affected (0.04 sec)
mysql&gt; 


mysql&gt; CREATE TABLE Employee(Employee_ID INT AUTO_INCREMENT PRIMARY KEY, Employee_Name VARCHAR(20) NOT NULL);
mysql&gt; INSERT INTO Employee (Employee_Name) VALUES('Billy Joe');
mysql&gt; INSERT INTO Employee (Employee_Name) VALUES('Mary Lou');
mysql&gt; INSERT INTO Employee (Employee_Name) VALUES('PJ Mara');
mysql&gt; 

CREATE TABLE Task
(
  Task_ID INT AUTO_INCREMENT PRIMARY KEY, 
  Task_Name VARCHAR(10) NOT NULL, 
  Task_Status INT NOT NULL, 
  Task_Emp INT NOT NULL, 
  Start_Date DATE NOT NULL, 
  End_Date DATE NOT NULL,
  FOREIGN KEY (Task_Status) REFERENCES Status(Status_ID),
  FOREIGN KEY (Task_Emp) REFERENCES Employee(Employee_ID)
);


INSERT INTO Task (Task_Name, Task_Status, Task_Emp, Start_Date, End_Date) VALUES('Task_1', 2, 3, '2016-03-17', '2016-04-17');
INSERT INTO Task (Task_Name, Task_Status, Task_Emp, Start_Date, End_Date) VALUES('Task_2', 3, 1, '2016-03-10', '2016-07-15');
INSERT INTO Task (Task_Name, Task_Status, Task_Emp, Start_Date, End_Date) VALUES('Task_3', 4, 2, '2016-04-13', '2016-05-17');
INSERT INTO Task (Task_Name, Task_Status, Task_Emp, Start_Date, End_Date) VALUES('Task_4', 1, 3, '2016-02-17', '2016-06-16');
INSERT INTO Task (Task_Name, Task_Status, Task_Emp, Start_Date, End_Date) VALUES('Task_5', 1, 1, '2016-04-17', '2016-07-20');
INSERT INTO Task (Task_Name, Task_Status, Task_Emp, Start_Date, End_Date) VALUES('Task_6', 2, 1, '2016-01-17', '2016-08-05');
INSERT INTO Task (Task_Name, Task_Status, Task_Emp, Start_Date, End_Date) VALUES('Task_7', 3, 2, '2016-02-17', '2016-06-09');
INSERT INTO Task (Task_Name, Task_Status, Task_Emp, Start_Date, End_Date) VALUES('Task_8', 4, 3, '2016-04-17', '2016-06-10');
INSERT INTO Task (Task_Name, Task_Status, Task_Emp, Start_Date, End_Date) VALUES('Task_9', 2, 2, '2016-04-17', '2016-05-12');
INSERT INTO Task (Task_Name, Task_Status, Task_Emp, Start_Date, End_Date) VALUES('Task_10', 1, 1, '2016-01-01', '2016-08-16');
INSERT INTO Task (Task_Name, Task_Status, Task_Emp, Start_Date, End_Date) VALUES('Task_11', 1, 1, '2016-01-01', '2016-02-28');