Querying the same table twice - 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

Querying the same table twice

Author

Topic

david_br89
Starting Member

4 Posts

Posted - 2012-08-01 : 20:43:24

Hi all,

I've been playing with this for a while but haven't got it quite figured.

Say I have a table called [Employees], with Fields 'ID', 'Level', 'Name' and 'Email'. A standard employee has all these fields complete with 'Level' set to '1'. If that employee is promoted to manager, then a new record is entered into the table with a different 'ID', same 'Name' as the standard employees record but with 'Level' now set to '2' and 'Email' left NULL. Now I want to pull all managers and their email, so i need to filter out those set to 'Level=2' but also return the email address from the 'Level=1' record where the 'Name' is the same.

I'm thinking this will be a self join exercise, but having never really gone beyond the very basics i'm getting a little lost in it. If anyone could give a hand on this that would be great.

Thanks,
Dave

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-01 : 20:46:44

you need to give the table an alias

Example :


SELECT e.emp_id, e.emp_name, e.manager_id, m.emp_name as managername
FROM   Employee e INNER JOIN Employee m ON e.manager_id = m.emp_id

KH
[spoiler]Time is always against us[/spoiler]

david_br89
Starting Member

4 Posts

Posted - 2012-08-01 : 21:28:03

Thanks khtan, but how would i filter out the managers email from their standard employee record on that?

SELECT m.Name, m.Email
FROM Employees e
INNER JOIN Employees m ON e.Name = m.Name
WHERE e.Level=2

I've joined on the names as the ID's are different between the employee and manager records, only the names are the same. Either way this still returns NULL for the email as its picking up the level 2 record that has no email associated with it. So i need it to find the manager record then link to the employee record on the name and pull the email from that, which is essentially then the managers email because the employee and manager record would refer to the same person.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-01 : 21:32:25

[code]
SELECT m.Name, m.Email
FROM Employees e
INNER JOIN Employees m ON e.Name = m.Name AND e.ID <> m.ID
WHERE e.Level=2
[/code]

KH
[spoiler]Time is always against us[/spoiler]

david_br89
Starting Member

4 Posts

Posted - 2012-08-01 : 22:09:36

Thanks, that did the trick!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-01 : 22:28:29

welcome

KH
[spoiler]Time is always against us[/spoiler]

david_br89
Starting Member

4 Posts

Posted - 2012-08-02 : 01:14:56

actually can I add another element to this question? There's another field in [Employees] called 'Assistant', containing the 'ID' of the standard employee record in the same table. Same as before, i need to pull the assistants email so want to link that field back to their standard employee record to get their email. So their's no type field to worry about, will it be just another alias reference?

SELECT m.Name, m.Email, a.Email
FROM   Employees e
       INNER JOIN Employees m ON e.Name = m.Name AND e.ID <> m.ID
       INNER JOIN Employees a ON a.Assistant=e.ID
WHERE  e.Level=2

Problem is that returns no results. I think its to do with a prior join limiting the [Employees] table to only manager records, hence it doesn't find the assistant and returns no result. How can i do a join to return everything in the [Employees] table then filter it out by the Managers 'Assitant' field to get the assistants record and email?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-02 : 09:29:43

or is it suppose to be ?

INNER JOIN Employees a ON e.Assistant = a.ID

KH
[spoiler]Time is always against us[/spoiler]

anchoredwisdom
Starting Member

22 Posts

Posted - 2012-08-02 : 10:03:57

Hi see if the below query helps.
I created on sample table empl
I am fetching employee name,his email and his assistant id from employee , i use the same table to fetch his asst email id
by joining assitant id from employee table to eid of assistant table.
SELECT Q1.ENAME,Q1.EMAIL,Q1.MID,Q2.EEMAIL FROM
(SELECT E.ENAME,E.EMAIL,M.EID AS MID,E.ASST AS AID FROM EMPL E,EMPL M
WHERE E.ENAME=M.ENAME AND M.LEVELS=2 AND E.EMAIL IS NOT NULL)Q1
JOIN
(SELECT A.EID AS AID,A.EMAIL AS EEMAIL FROM EMPL A)Q2
ON
Q1.AID=Q2.AID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources