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 +
++++++++++++++++++++++++++++++++++++

Re: Simple Join (maybe?) 80 80

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
Re: Simple Join (maybe?) 80 80

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
Re: Simple Join (maybe?) 80 80

Thanks guys. Both where sufficient.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.18 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.