Help with Joins
-
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?
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
-
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 -
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.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
-
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.
-
Exactly I too meant the same, Thanks for elaborating. I believe hawg already has the solution for it.
Prasad Bhogadi
www.inforaise.com -
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.
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply