MS SQL

MS SQL RSS

Simple Join (maybe?)

Please support our MS SQL advertiser: Intel Parallel Studio Home

Thread Solved

•

Join Date: Apr 2009

Posts: 2

Reputation: Summerston is an unknown quantity at this point

Solved Threads: 0

Summerston Summerston is offline

Offline

Newbie Poster

Simple Join (maybe?)

Apr 14th, 2009

•

Join Date: Aug 2007

Posts: 793

Reputation: darkagn has a spectacular aura about

Solved Threads: 110

darkagn

Offline

Master Poster

Re: Simple Join (maybe?)

Apr 14th, 2009

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:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
-- 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
-- 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

Last edited by darkagn; Apr 14th, 2009 at 10:26 am. Reason: welcome!

There are no stupid questions, only those too stupid to ask for help.
echo is a web developer's best friend.

•

Join Date: Aug 2006

Posts: 2,065

Reputation: Ramy Mahrous is just really nice

Solved Threads: 256

Ramy Mahrous Ramy Mahrous is offline

Offline

Postaholic

Re: Simple Join (maybe?)

Apr 14th, 2009

Here you're

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
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
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