Identify Front-Line Leader - Inner Join? 2

[robmkimmons]

I have an employee table tbl_Master that has a list of employees including their position number [PosNum]. Another field in the table identifies who they report to [RptToPosNum]. I am trying to query the "front-line" leaders aka: Employees who's direct reports do not have direct reports of their own. Below is the simplified version of the table--

tbl_Master:
[EmpName]
[PosNum]
[RptToPosNum]

Here is the SQL:

SELECT tbl_Master_1.Name AS SupvName, tbl_Master_1.PosNum
FROM tbl_Master INNER JOIN tbl_Master AS tbl_Master_1 ON tbl_Master.RptToPosNum = tbl_Master_1.PosNum;

The PROBLEM is this brings up any leader who has a direct report (meaning it could be a leader of a leader). I want to pull out only those leaders who's direct reports have no direct reports. I have been racking my brain on this and i'm sure there is a simple solution.

Thanks!

~Rob

If we expect the unexpected, does that make the unexpected... well, expected?

 

[PHV]

What about this clause ?
WHERE tbl_Master_1.RptToPosNum IS NULL

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[robmkimmons]

Thanks for the reply PHV,

Unless i'm missing something, that's going the wrong direction. All employees in this table report to someone so there is always a value in that field.


SELECT tbl_Master_1.Name AS SupvName, tbl_Master_1.PosNum
FROM tbl_Master INNER JOIN tbl_Master AS tbl_Master_1 ON tbl_Master.RptToPosNum = tbl_Master_1.PosNum
WHERE tbl_Master_1.RptToPosNum IS NULL; ...Returns Nothing

~Rob

If we expect the unexpected, does that make the unexpected... well, expected?

 

[lameid]

Unless i'm missing something, that's going the wrong direction.

Well then obviously you would use the other table (alias) in the where clause (tbl_Master instead of tbl_Master_1).

All employees in this table report to someone so there is always a value in that field.

If that is true then you need data not in the table you provided to solve it or have misstated your problem.

 

[robmkimmons]

Maybe this helps to illustrate it visually:

*Note - The numerical "levels" used below are for explanatory purposes, the real data uses arbitrary numbers.

100's = Individual Contributor (not listed in [RptToPosNum] field because no one reports to them)
200's = Front-Line supervisor (has at least one direct report that has no direct reports of their own)
300's = Mgr (has at least one 200 level reporting to it)
400's = Sr Mgr

[PosNum] [RptToPosNum]
101 200
102 200
103 300
200 300
300 400
400 500

So from the data above, the ONLY record I wish to retrieve is "200" because it is the only position number that has all those reporting to it not having others reporting to them as well. For example, "300" has both a "100" and a "200" reporting to it, but since it has at least one "200" reporting to it, I don't want it retrieved. *whew!* Reading back over this I don't know if it helped or not! lol


~Rob

If we expect the unexpected, does that make the unexpected... well, expected?

 

[lameid]

Select
    [EmpName]
    [PosNum]
    [RptToPosNum]

From tbl_Master
Where RptToPosNum IN (Select PosNom From tbl_Master 
                     Where RptToPosNum Not In (Select PosNom From tbl_Master)
                     )

 

[robmkimmons]

Okay, so this is what I came up with:

SELECT tbl_Master.Name, tbl_Master.PosNum, tbl_Master.RptToPosNum
FROM tbl_Master
WHERE (((tbl_Master.RptToPosNum) In (Select [PosNum] FROM [tbl_Master] WHERE [RptToPosNum] Not In (SELECT [PosNum] FROM [tbl_Master]))));


...runs but produces no results.

~Rob

If we expect the unexpected, does that make the unexpected... well, expected?

 

[Gammachaser]

Try this:

SELECT 
[EmpName]
[PosNum]
[RptToPosNum]

FROM tblMaster 
WHERE [PosNum] IN(SELECT [RptToPosNum] FROM tblMaster WHERE [RptToPosNum] Not In(SELECT [RptToPosNum] FROM tblMaster WHERE [PosNum] IN(SELECT [RptToPosNum] FROM tblMaster)) );

I think it works, but I got very confused when trying to test it!

 

[lameid]

That hurt my head for a moment... Took me a minute to see what I missed. Kudos, Gammachaser, I had a hard enough time following the missing step let alone figuring it out.

 

[Gammachaser]

@lameid -- Thanks.

I think the only hope of figuring it out is to work it from the inside out. It hurt my head a bit, too.