Very basic SQL query question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Very basic SQL query question

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_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
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_name
from person, manager_allocation
where person.person_id = manager_allocation.manager_id

how 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 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

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 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

105ben
Starting Member

16 Posts

Posted - 2012-12-20 : 17:21:34

I think the line

SELECT p.person.person_id,

should read

SELECT 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 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
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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources