Ad:

MS SQL Discussion Thread
Marked Solved
Views: 579

Apr 14th, 2009

Simple Join (maybe?)

Expand Post »

Similar Threads

formulating a sql query.Its urgent ! in MS Access and FileMaker Pro
update two rows in one query in MS SQL
problem for a simple asp.net query in ASP
Urgent help required in Oracle

Summerston

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Apr 2009

Permalink

Apr 14th, 2009

Re: Simple Join (maybe?)

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:

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!

darkagn

Reputation Points: 395

Solved Threads: 192

Veteran Poster

Offline

1,136 posts
since Aug 2007

Permalink

Apr 14th, 2009

Re: Simple Join (maybe?)

Here you're

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

Ramy Mahrous

Featured Poster

Reputation Points: 480

Solved Threads: 276

Postaholic

Offline

2,189 posts
since Aug 2006

Permalink

Apr 14th, 2009

Re: Simple Join (maybe?)

Thanks guys. Both where sufficient.

Summerston

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

2 posts
since Apr 2009

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: determining if an SQL database exists, etc.

Next Thread in MS SQL Forum Timeline: Removing duplicates from INNER JOIN of two tables

DaniWeb Message

Cancel Changes

User Name
Password