How to query a table that points to itself

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

How to query a table that points to itself

Author

Topic

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-05-21 : 23:41:26

Dear experts,

Hi, I got a table name Employee, with the following fields

EmployeeID
Name,
ManagerID
ManagerID2,
ManagerID3,
ManagerID4 ..

ManagerID, ManagerID2, ManagerID3, ManagerID4 are all pointing to EmployeeID in the same table.

Now I want to select the all employee records where the manager NAME is like '%Raymond%'. (i.e. any of the four managers name is like '%Raymond%'.

How do I construct the select query? I tried this, but it does not work if there are more than 1 manager named 'Raymond'

DECLARE @Name varchar(50)
SET @Name = '%Raymond%'

DECLARE @EmployeeNumber nvarchar(50)
Select
@EmployeeNumber = EmployeeID
from
Employee
where
[Name] like @Name

SELECT
'HumanResources.Employee' as TableName,
E.[Name] as EmployeeName ,
isnull((Select [Name] from Employee E2 where E2.EmployeeID = E.ManagerID),'') as Manager1,
isnull((Select [Name] from Employee E2 where E2.EmployeeID = E.ManagerID2),'') as Manager2,
isnull((Select [Name] from Employee E2 where E2.EmployeeID = E.ManagerID3),'') as AppraisingManager,
isnull((Select [Name] from Employee E2 where E2.EmployeeID = E.ManagerID4),'') as ModeratingManager,
E.CurrentFlag as Active
FROM
HumanResources.Employee E

WHERE
(E.[ManagerID] = @EmployeeNumber)
or (E.[ManagerID2] = @EmployeeNumber)
or (E.[AppraisingManagerID] = @EmployeeNumber)
or (E.[ModeratingManagerID] = @EmployeeNumber))

Any help is very much appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-22 : 01:37:26

You are searching by EmployeeNumber and using a variable to hold the value. If you've more than 1 manager only one of value will be stored in your variable and it will return only those records which matches the employee number.
You can make it work like this. either store the employee numbers in a table variable and take join with in in your select statement or use the name itself to search for records.

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-05-22 : 02:19:55

Please can you kindly guide me on how I could select by name for the above mentioned query?

The bottleneck I am hitting in my attempt to get the results by name is that the ManagerID to ManagerID4 is nullable, so when I try to join them, sometimes I end up with less rows than the query.

I did my best, but still could not get it done correctly. Help is very much appreciated. Thank you very much.

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-05-22 : 03:13:30

Please I badly require some help... I could not seem to get the query right.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-05-22 : 03:32:10

[code]DECLARE @Name VARCHAR(50)
SET @Name = '%Raymond%'

DECLARE @Employee TABLE (Number NVARCHAR(50))

INSERT @Employee
SELECT EmployeeID
FROM Employee
WHERE [Name] LIKE @Name

SELECT 'HumanResources.Employee' as TableName,
E.[Name] as EmployeeName ,
isnull(e1.[Name], '') as Manager1,
isnull(e2.[Name], '') as Manager2,
isnull(e3.[Name], '') as AppraisingManager,
isnull(e4.[Name], '') as ModeratingManager,
E.CurrentFlag as Active
FROM HumanResources.Employee AS E
LEFT JOIN Employee AS e1 ON e1.EmployeeID = e.ManagerID1
LEFT JOIN Employee AS e2 ON e2.EmployeeID = e.ManagerID2
LEFT JOIN Employee AS e3 ON e3.EmployeeID = e.ManagerID3
LEFT JOIN Employee AS e4 ON e4.EmployeeID = e.ManagerID4
INNER JOIN @Employee AS x ON x.Number IN (e1.EmployeeID, e2.EmployeeID, e3.EmployeeID, e4.EmployeeID)[/code]

E 12°55'05.25"
N 56°04'39.16"

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-05-22 : 04:13:54

Peso,

Thank you very much! Did anyone tell you that you are a gem? I was about to literially tear my hair out..

Once again, thank you for your kind guidance. I really appreciate that you took the time to show me how to do it. Learnt something new about nested query today. God bless you!

Eugene

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources