| Author |
 Topic  |
|
|
105ben
Starting Member
United Kingdom
16 Posts |
 Posted - 12/20/2012 : 16:22:52
|
Hey, I'm completely new to SQL but trying to learn.
I have two tables, person and manager_allocation. Person stores details on all the people in an organisation, manager_allocation stores details of which person's manage which other person's.
PERSON (person_id, name)
MANAGER_ALLOCATION(allocation_id, manager_id, person_id)
manager_id is actually their person_id from the PERSON table. person_id in MANAGER_ALLOCATION is a foreign key.
Basically, I want to run a query that returns a persons id, name, their managers id and their managers name. So far I have this:
SELECT person.person_id, firstname, manager_id,
from person, manager_allocation
where person.person_id = manager_allocation.person_id
this does not give me the managers name, just their ID. I cant figure out how to get there name from the person table.
Would really appreciate some help here! Thanks |
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7174 Posts |
Posted - 12/20/2012 : 16:29:41
|
| Again you need to do self join for manager_id and person Person_id to get the name |
 |
|
|
105ben
Starting Member
United Kingdom
16 Posts |
Posted - 12/20/2012 : 16:33:14
|
| Thanks, but how do I do that? |
|
|
|
105ben
Starting Member
United Kingdom
16 Posts |
Posted - 12/20/2012 : 16:50:16
|
I think I see what you mean. So now I have this aswell:
select manager_id, firstname as manager_name
from person, manager_allocation
where person.person_id = manager_allocation.manager_id
how do I combine the two queries? |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5155 Posts |
Posted - 12/20/2012 : 17:04:33
|
You do it in the same query - but use different aliases as shown below (I changed the joins to ANSI joins, you can use where clause in a similar manner if you like)SELECT p.person.person_id,
p.firstname,
ma.manager_id,
pm.firstname AS ManagerFirstname
FROM person p
INNER JOIN manager_allocation ma
ON ma.person_id = p.person_id
INNER JOIN person pm
ON pm.person_id = ma.manager_id
|
|
|
|
105ben
Starting Member
United Kingdom
16 Posts |
Posted - 12/20/2012 : 17:13:37
|
Thanks, starting to make a lot more sense!
are the p ma pm all the different aliases?
and I'm not 100% sure how/why they're being used, any chance you could explain? sorry to be a pain |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5155 Posts |
Posted - 12/20/2012 : 17:16:24
|
In the query, we are using the same table - person - twice. So when you refer to a column in the person table, SQL Server does not know which instance you are referring to. So you give aliases - I sort of shortened it by omitting an optional keyword "AS". So, p, ma, and pa are aliases. See here for details.SELECT p.person.person_id,
p.firstname,
ma.manager_id,
pm.firstname AS ManagerFirstname
FROM person AS p
INNER JOIN manager_allocation AS ma
ON ma.person_id = p.person_id
INNER JOIN person AS pm
ON pm.person_id = ma.manager_id |
Edited by - sunitabeck on 12/20/2012 17:17:12 |
|
|
|
105ben
Starting Member
United Kingdom
16 Posts |
Posted - 12/20/2012 : 17:21:34
|
I think the line
SELECT p.person.person_id,
should read
SELECT p.person_id,
is that right? |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5155 Posts |
Posted - 12/20/2012 : 18:59:21
|
Yes! Yes!! Sorry about that.  |
|
|
|
sodeep
Flowing Fount of Yak Knowledge
USA
7174 Posts |
Posted - 12/20/2012 : 20:28:52
|
SELECT p.person.person_id,
p.firstname,
ma.manager_id,
pm.firstname AS ManagerFirstname
FROM person AS p
INNER JOIN manager_allocation AS ma
ON ma.person_id = p.person_id
LEFT JOIN person AS pm
ON pm.person_id = ma.manager_id
|
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5155 Posts |
Posted - 12/21/2012 : 07:53:31
|
Thanks sodeep!
105ben, although sodeep did not elaborate on the change he did (changing the last join to a LEFT JOIN), it is a required change. The reason is that there has to be at least one employee (the CEO?) who does not report to another employee - i.e., the manager_id column has a null value. If you used an inner join, that employee would be eliminated. Left join ensures that you will get that employee also in the results. |
|
|
| |
Topic |
|
Very basic SQL query question