SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Very basic SQL query question
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

105ben
Starting Member

United Kingdom
16 Posts

Posted - 12/20/2012 :  16:22:52  Show Profile  Reply with Quote
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  Show Profile  Reply with Quote
Again you need to do self join for manager_id and person Person_id to get the name
Go to Top of Page

105ben
Starting Member

United Kingdom
16 Posts

Posted - 12/20/2012 :  16:33:14  Show Profile  Reply with Quote
Thanks, but how do I do that?
Go to Top of Page

105ben
Starting Member

United Kingdom
16 Posts

Posted - 12/20/2012 :  16:50:16  Show Profile  Reply with Quote
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?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/20/2012 :  17:04:33  Show Profile  Reply with Quote
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
Go to Top of Page

105ben
Starting Member

United Kingdom
16 Posts

Posted - 12/20/2012 :  17:13:37  Show Profile  Reply with Quote
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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/20/2012 :  17:16:24  Show Profile  Reply with Quote
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
Go to Top of Page

105ben
Starting Member

United Kingdom
16 Posts

Posted - 12/20/2012 :  17:21:34  Show Profile  Reply with Quote
I think the line

SELECT p.person.person_id,

should read

SELECT p.person_id,

is that right?
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/20/2012 :  18:59:21  Show Profile  Reply with Quote
Yes! Yes!! Sorry about that.
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/20/2012 :  20:28:52  Show Profile  Reply with Quote

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
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/21/2012 :  07:53:31  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000