Author |
Topic |
105ben
Starting Member
16 Posts |
Posted - 2012-12-20 : 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_allocationwhere person.person_id = manager_allocation.person_idthis 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
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-20 : 16:29:41
|
Again you need to do self join for manager_id and person Person_id to get the name |
|
|
105ben
Starting Member
16 Posts |
Posted - 2012-12-20 : 16:33:14
|
Thanks, but how do I do that? |
|
|
105ben
Starting Member
16 Posts |
Posted - 2012-12-20 : 16:50:16
|
I think I see what you mean. So now I have this aswell:select manager_id, firstname as manager_namefrom person, manager_allocationwhere person.person_id = manager_allocation.manager_idhow do I combine the two queries? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-20 : 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 ManagerFirstnameFROM 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
16 Posts |
Posted - 2012-12-20 : 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
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-20 : 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 ManagerFirstnameFROM 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 |
|
|
105ben
Starting Member
16 Posts |
Posted - 2012-12-20 : 17:21:34
|
I think the lineSELECT p.person.person_id,should readSELECT p.person_id,is that right? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-20 : 18:59:21
|
Yes! Yes!! Sorry about that. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2012-12-20 : 20:28:52
|
SELECT p.person.person_id, p.firstname, ma.manager_id, pm.firstname AS ManagerFirstnameFROM 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
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-21 : 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. |
|
|
|