Help with Joins

Question

Help with Joins

hawg

SSCertifiable

Points: 6044
More actions
March 9, 2004 at 8:08 am

#107934

Here's a sample query I am writing:
select a.department, a.mgr_id, a.emp1_id, a.emp2_id
from dept_table a
order by a.department
The output from this query will look like:
ACCT 123     456     789
IT      246     468     680
Here's my problem. The name for each person (mgr, emp1, emp2) is stored in a table called, for example, employees.
I would like to be able to get the name of each person in this query at one time. I would like to see the output look like this:
ACCT 123   Bob 456 Tom   789 Susan
IT       246 Tim   468 Mary 680 Scott
I don't know how I can make the join(s) work so that I can get all of the names for one record returned.
Any ideas?

----------------------------------------------------------
01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 1

Use the same table with Alias for each field like employees mgr on mgr.id=a.mgr_id , employees emp1 on emp1.id=a.emp1_id so on..

Prasad Bhogadi
www.inforaise.com

hawg SSCertifiable Points: 6044 More actions · Answer 2

D'oh! I had a Homer moment!

It makes sense. I just got stuck in one train of thought and I couldn't see the answer right in front of my eyes.

Thanks.

----------------------------------------------------------

01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011

Tobi White Mr or Mrs. 500 Points: 551 More actions · Answer 3

select a.department,a.mgr_id,b.mgr,a.emp1_id,c.emp1,a.emp2_id,d.emp2

from dept_table a

join employees b

on a.mgr_id = b.emp_id

join employees c

on a.emp1_id = c.emp_id

join employees d

on a.emp2_id = d.emp_id

order by a.department

Just replace emp_id with whatever the column name is in employees that represents the employee ID.

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 4

Exactly I too meant the same, Thanks for elaborating. I believe hawg already has the solution for it.

Prasad Bhogadi
www.inforaise.com

hawg SSCertifiable Points: 6044 More actions · Answer 5

Yeah, thanks to both of you. I immediately realized the answer with Prasad's response. I just got myself pigeon-holed and missed figuring it out on my own.

Thanks again.

----------------------------------------------------------

01010011010100010100110000100000010100110110010101110010011101100110010101110010
001000000101001001101111011000110110101101110011