0

What SQL statement would I need to join the employees/work orders tables to create the desired output (below)?

++++++++++++++++++++
+ employees +
++++++++++++++++++++
+ id | name +
++++++++++++++++++++
+ 1 | Jim Smith +
+ 2 | Susie Helms +
++++++++++++++++++++

++++++++++++++++++++++++++++++++++++
+ Work Orders +
++++++++++++++++++++++++++++++++++++
+ id | submitted_by | completed_by +
++++++++++++++++++++++++++++++++++++
+ 1 | 1 | 2 +
+ 2 | 2 | 1 +
+ 3 | 2 | 2 +
++++++++++++++++++++++++++++++++++++

++++++++++++++++++++++++++++++++++++
+ Desired Output +
++++++++++++++++++++++++++++++++++++
+ id | submitted_by | completed_by +
++++++++++++++++++++++++++++++++++++
+ 1 | Jim Smith | Susie Helms +
+ 2 | Susie Helms | Jim Smith +
+ 3 | Susie Helms | Susie Helms +
++++++++++++++++++++++++++++++++++++

3
Contributors
3
Replies
4
Views
8 Years
Discussion Span
Last Post by Summerston
0

Hi Somerston and welcome to DaniWeb :)

You will need to join to the employee table twice to get the name for each column. Something like this should work:

-- first select the columns required
SELECT wo.id, em.name as submitted_by, emp.name as completed_by 
-- not sure if you can use these aliases
-- because they are also column names of the WorkOrders table
-- if not, rename them to submitted_name and completed_name for example

-- from tables
FROM WorkOrders wo
INNER JOIN employee em
-- link submitted_by to id of employee
ON wo.submitted_by = em.id
INNER JOIN employee emp
-- link completed_by to id of employee
ON wo.completed_by = emp.id
0

Here you're

SELECT     WorkOrders.ID, employees.name, employees_1.name AS Expr1
FROM         WorkOrders LEFT OUTER JOIN
                      employees AS employees_1 ON WorkOrders.submitted_by = employees_1.id LEFT OUTER JOIN
                      employees AS employees ON WorkOrders.completed_by = employees.id
This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.