Self join. Employees and supervisors query.

[Jim Andersen]

I have a problem with this standard employee-supervisor scenario

For pictures:
http://www.databasedev.co.uk/self-join_query.html

I want to show all employees "belonging" to a specific supervisor.

E.g, EMP1 and EMP2 both has the same supervisor SUP1. EMP3 has a supervisor
SUP2. EMP4 has supervisor SUP3.

SUP1 and SUP2 in turn have the same boss SUP4.

I now want to create a form, with two listboxes, lb1 and lb2.
In lb1 I show the supervisors and in lb2, I show ALL employees.
I then select SUP1 form lb1. lb2 now only shows EMP1 and EMP2 since they
"belong" to SUP1.

If I select SUP4 in lb1, I want lb2 to show EMP1, EMP2, SUP1 and SUP2, since
they are all SUP4's underlings.

Bonus question:

I want to be able to work with an unknown number of levels in the
employee-supervisor structure.
So I want to create a form with ... hm... lets say 5 listboxes (I can figure
out how to create those dynamically on my own...)
And then a listbox that shows the employees.
The first listbox, should show the top-level supervisor (those that do not
have a supervisor).
The second listbox should show all second-level supervisors, that is, those
who have a supervisor, but not those whose supervisor has a supervisor.
And so on...
When you first open the form, all employees are shown in the listbox.
If you select a supervisor in any of the listboxes, the list is shortened.
If you select one of the upper-level supervisors both the other comboboxes
and the listbox should be shortened.

Any and all help appreciated,

/jim

[John Welch]

Not sure about the initial showing of all the different levels of
supervisors (i.e. people whose supervisors have only one supervisor are
shown in a particular box.), but, you could have the first listbox show
everyone with no supervisor by setting the rowsource of the box to:
SELECT employeeID, Name from qryEmployeesPlusSupervisors where
isnull(supervisorID)
then you could set the second listbox's rowsource to:
SELECT employeeID, Name from qryEmployeesPlusSupervisors where supervisorID=
forms!yourform.lstBox1
and requery the second list box in the afterupdate event of the first
listbox.
Then you'd do the same thing for the rest of the listboxes, having each
refer to the previous one for the criterion of its rowsource.
Then as you worked your way from the top down, each list box would show
people who are supervised by the person selected in the previous box.

hope this helps
-John

"Jim Andersen" <jb****@politi.dk> wrote in message
news:43*********************@dread11.news.tele.dk. ..

I have a problem with this standard employee-supervisor scenario

For pictures:
http://www.databasedev.co.uk/self-join_query.html

I want to show all employees "belonging" to a specific supervisor.

E.g, EMP1 and EMP2 both has the same supervisor SUP1. EMP3 has a
supervisor SUP2. EMP4 has supervisor SUP3.

SUP1 and SUP2 in turn have the same boss SUP4.

I now want to create a form, with two listboxes, lb1 and lb2.
In lb1 I show the supervisors and in lb2, I show ALL employees.
I then select SUP1 form lb1. lb2 now only shows EMP1 and EMP2 since they
"belong" to SUP1.

If I select SUP4 in lb1, I want lb2 to show EMP1, EMP2, SUP1 and SUP2,
since they are all SUP4's underlings.

Bonus question:

I want to be able to work with an unknown number of levels in the
employee-supervisor structure.
So I want to create a form with ... hm... lets say 5 listboxes (I can
figure out how to create those dynamically on my own...)
And then a listbox that shows the employees.
The first listbox, should show the top-level supervisor (those that do not
have a supervisor).
The second listbox should show all second-level supervisors, that is,
those who have a supervisor, but not those whose supervisor has a
supervisor.
And so on...
When you first open the form, all employees are shown in the listbox.
If you select a supervisor in any of the listboxes, the list is shortened.
If you select one of the upper-level supervisors both the other comboboxes
and the listbox should be shortened.

Any and all help appreciated,

/jim

[Jim Andersen]

"John Welch" <j+ohnw+elch@cal+central.com (remove +'s)> skrev i en
meddelelse news:dn*********@enews4.newsguy.com...

Not sure about then you could set the second listbox's rowsource to:
SELECT employeeID, Name from qryEmployeesPlusSupervisors where
supervisorID= forms!yourform.lstBox1
Yes, but that would only work if I select an entry in listbox1
hope this helps

Not really :-) but thanks for trying.

/jim

[Mike Preston]

On Thu, 15 Dec 2005 14:23:53 +0100, "Jim Andersen" <jb****@politi.dk>
wrote:

I have a problem with this standard employee-supervisor scenario

For pictures:
http://www.databasedev.co.uk/self-join_query.html

I want to show all employees "belonging" to a specific supervisor.

E.g, EMP1 and EMP2 both has the same supervisor SUP1. EMP3 has a supervisor
SUP2. EMP4 has supervisor SUP3.

SUP1 and SUP2 in turn have the same boss SUP4.

I now want to create a form, with two listboxes, lb1 and lb2.
In lb1 I show the supervisors and in lb2, I show ALL employees.
I then select SUP1 form lb1. lb2 now only shows EMP1 and EMP2 since they
"belong" to SUP1.

If I select SUP4 in lb1, I want lb2 to show EMP1, EMP2, SUP1 and SUP2, since
they are all SUP4's underlings.

Bonus question:

I want to be able to work with an unknown number of levels in the
employee-supervisor structure.
So I want to create a form with ... hm... lets say 5 listboxes (I can figure
out how to create those dynamically on my own...)
And then a listbox that shows the employees.
The first listbox, should show the top-level supervisor (those that do not
have a supervisor).
The second listbox should show all second-level supervisors, that is, those
who have a supervisor, but not those whose supervisor has a supervisor.
And so on...
When you first open the form, all employees are shown in the listbox.
If you select a supervisor in any of the listboxes, the list is shortened.
If you select one of the upper-level supervisors both the other comboboxes
and the listbox should be shortened.

Any and all help appreciated,

Bonus answer: do your own homework.

However, with that said, if you have attempted something, show your
work and state your rationale. You might get SOME help if you do
that.

mike

[Jim Andersen]

"Mike Preston" <mb******@pacbell.net.invalid> skrev i en meddelelse
news:43****************@news.INDIVIDUAL.NET...

On Thu, 15 Dec 2005 14:23:53 +0100, "Jim Andersen" <jb****@politi.dk>
wrote:

I have a problem with this standard employee-supervisor scenario

Bonus answer: do your own homework.
Huh ?
However, with that said, if you have attempted something, show your
work and
Didn't I do that ? I described the tables, the forms, and the
main/troublesome query.
What else do you need ? Be specific.
state your rationale.

Could you rephrase that ?

/jim

[Mike Preston]

On Fri, 16 Dec 2005 12:50:38 +0100, "Jim Andersen" <jb****@politi.dk>
wrote:

"Mike Preston" <mb******@pacbell.net.invalid> skrev i en meddelelse
news:43****************@news.INDIVIDUAL.NET...

On Thu, 15 Dec 2005 14:23:53 +0100, "Jim Andersen" <jb****@politi.dk>
wrote:

I have a problem with this standard employee-supervisor scenario

Bonus answer: do your own homework.

Huh ?

This looks like an assignment given in class. Am I wrong? If so, say
so.
However, with that said, if you have attempted something, show your
work and

Didn't I do that ? I described the tables, the forms, and the
main/troublesome query.
What else do you need ? Be specific.

You are asking ME to be specific. That gave me a giggle.

You say something needs to get done. You say you want "help." That
means you tried something. What did you try? Be specific. I'll even
help you on what "specific" means: if you have crafted any queries,
post them and state their context (where they are used and what you
are intending them to do).

state your rationale.

Could you rephrase that ?

Explain why you did what you detailed above.

It is amazing how going through the process I've just described will
sometimes turn the lightbulb on.

mike

[Jim Andersen]

>>>>I have a problem with this standard employee-supervisor scenario

This looks like an assignment given in class. Am I wrong? If so, say
so.
No class assignment. I'm building an app for my company. I chose the
employee-supervisor scenario because it is well-known (so I could save a lot
of explanations) and I thought someone had already extended on it, to do
what I want it to do.
You are asking ME to be specific. That gave me a giggle.
I must have explained it poorly then. Sorry. Or you haven't read the post,
just skimmed it.
You say something needs to get done.
No. I say I need help with a specific query. 1 query. I explain in detail
what that query should return. I can't see how I can be more specific.

Or, that someone says "You can't make such a query." Then I'll try building
some temp tables, or using some stored procedures or whatever.
You say you want "help." That
means you tried something. What did you try? Be specific. I'll even
help you on what "specific" means: if you have crafted any queries,
post them and state their context (where they are used and what you
are intending them to do).
I outlined the tables (with the key fields), I designed the forms layout
(user interface), and the queries needed to populate most of them (either in
my post or the example I linked to). And then I got stuck when trying to
create the query, thats a little more advanced than the example.

If you still don't understand what the query should return, I'll rephrase
it, and come up with some testdata. I can post some "Create Tables" and
"insert" statements for Sqlserver so you can see it in real life, if that
will help.
Explain why you did what you detailed above.
To get the app to work the way I want it to.
It is amazing how going through the process I've just described will
sometimes turn the lightbulb on.

Yes. I've been doing that for 20 years now.

/jim

[Mike Preston]

On Thu, 15 Dec 2005 14:23:53 +0100, "Jim Andersen" <jb****@politi.dk>
wrote:

I have a problem with this standard employee-supervisor scenario

For pictures:
http://www.databasedev.co.uk/self-join_query.html

I want to show all employees "belonging" to a specific supervisor.

E.g, EMP1 and EMP2 both has the same supervisor SUP1. EMP3 has a supervisor
SUP2. EMP4 has supervisor SUP3.

SUP1 and SUP2 in turn have the same boss SUP4.

I now want to create a form, with two listboxes, lb1 and lb2.
In lb1 I show the supervisors and in lb2, I show ALL employees.
I then select SUP1 form lb1. lb2 now only shows EMP1 and EMP2 since they
"belong" to SUP1.

If I select SUP4 in lb1, I want lb2 to show EMP1, EMP2, SUP1 and SUP2, since
they are all SUP4's underlings.
I assume you are working with the same table layout as in the website.
If so, the website gives you everything you need in order to do a
self-join, which is what you are looking for, isn't it?

Bunch of aircode follows (which means that if somebody spots something
that doesn't work: a) I wouldn't be surprised and b) I'd appreciate it
if somebody points out what does).

The rowsource for lb1 is something like:

Select DistinctRow ID, strLastName, strFirstName from tblSupervisors a
inner join tblSupervisors b on a.ID=B.lngSupervisorID

In the after update event of lb1 put:

lb2.Rowsource = "Select ID, strLastName,StrFirstName from
tblSupervisors where lngSupervisorID = " & lb1.value
lb2.Requery
Bonus question:

I want to be able to work with an unknown number of levels in the
employee-supervisor structure.
So I want to create a form with ... hm... lets say 5 listboxes (I can figure
out how to create those dynamically on my own...)
And then a listbox that shows the employees.
The first listbox, should show the top-level supervisor (those that do not
have a supervisor).
In the real world, the only person without a supervisor is the
Chairman of the Board. But I'll play along.

As reported by John, a rowsource for this one might be:

SELECT ID, strLastName,StrFirstName from tblSupervisors where
isnull(lngSupervisorID)
The second listbox should show all second-level supervisors, that is, those
who have a supervisor, but not those whose supervisor has a supervisor.
Select DistinctRow ID, strLastName, StrFirstName from (tblSupervisors
a inner join tblSupervisors b on a.ID=b.lngSupervisorID) inner join
tblSupervisors c on a.lngSupervisor=c.ID where
Isnull(c.lngSupervisorID)

Try that and let me know if if works. I'm a bit tired at the moment,
so it may not do just what you want.
And so on...
The rest is left to you.
When you first open the form, all employees are shown in the listbox.
Which listbox? The first? The second? I think you mean the second.
The second listbox is the same rowsource as the second list box above:

lb2.Rowsource = "Select ID, strLastName,StrFirstName from
tblSupervisors where lngSupervisorID = " & lb1.value
lb2.Requery
If you select a supervisor in any of the listboxes, the list is shortened.
What list? The list of employees in the second listbox?
If you select one of the upper-level supervisors both the other comboboxes
and the listbox should be shortened.

Comboboxes? Listboxes? Are you a bit confused? Maybe I am a bit
confused? Maybe we are both a bit confused?

What is it that you really want to do?

mike

[Terry Kreft]

The standard way of way of getting the hierarchy is to extend the model, so
the base query they show, you just add further aliased tables and continue
to link between employee and supervisor.

The problem with the model shown on the website you quote is that it only
shows people who have subordinates,, therefore if you extend the model to a
third level you only get people who have people reporting to them who have
people reporting to them (if you see what I mean).

So say we have the following data in tblEmps

EmpID SupID Name
1 0 Terry
2 0 Jim
3 1 Fred
4 1 Joe
5 2 Abby
6 2 Martin
7 3 Ian
8 3 Paul

And we extend the model as shown in a query
SELECT
E1.Name, E2.Name, E3.Name
FROM
(tblEmps AS E1
INNER JOIN tblEmps AS E2
ON E1.EmpID = E2.SupID)
INNER JOIN
tblEmps AS E3 ON E2.EmpID = E3.SupID;

We get the following results
E1.Name E2.Name E3.Name
Terry Fred Ian
Terry Fred Paul

If we change the query to use outer joins though
SELECT
E1.Name, E2.Name, E3.Name
FROM
(tblEmps AS E1
LEFT JOIN tblEmps AS E2
ON E1.EmpID = E2.SupID)
LEFT JOIN
tblEmps AS E3 ON E2.EmpID = E3.SupID;

We get the following
E1.Name E2.Name E3.Name
Terry Fred Ian
Terry Fred Paul
Terry Joe
Jim Abby
Jim Martin
Fred Ian
Fred Paul
Joe
Abby
Martin
Ian
Paul

Which is getting pretty close to what you want .

The details of filter for the display I'll leave to you.

Now dynamically creating this becomes a bit more difficult The first thing
you need to do is work out how many levels of tables you need in the query,
luckily there is a fairly easy way to work this out. If we go back to the
first Query above.
SELECT
E1.Name, E2.Name, E3.Name
FROM
(tblEmps AS E1
INNER JOIN tblEmps AS E2
ON E1.EmpID = E2.SupID)
INNER JOIN
tblEmps AS E3 ON E2.EmpID = E3.SupID;

.... and add another copy of the table to it.
SELECT
E1.Name, E2.Name, E3.Name, E4.Name
FROM
((tblEmps AS E1
INNER JOIN tblEmps AS E2
ON E1.EmpID = E2.SupID)
INNER JOIN
tblEmps AS E3
ON E2.EmpID = E3.SupID)
INNER JOIN tblEmps AS E4
ON E3.EmpID = E4.SupID;

We get the following resultset
E1.Name E2.Name E3.Name E4.Name
i.e. an empty recordset.. Therefore we no know the hierarchy does not
extend to the 4th level and can step back to the 3rd level where we use the
outer join version to create our lists.
--
Terry Kreft

"Jim Andersen" <jb****@politi.dk> wrote in message
news:43*********************@dread11.news.tele.dk. ..

I have a problem with this standard employee-supervisor scenario

For pictures:
http://www.databasedev.co.uk/self-join_query.html

I want to show all employees "belonging" to a specific supervisor.

E.g, EMP1 and EMP2 both has the same supervisor SUP1. EMP3 has a
supervisor SUP2. EMP4 has supervisor SUP3.

SUP1 and SUP2 in turn have the same boss SUP4.

I now want to create a form, with two listboxes, lb1 and lb2.
In lb1 I show the supervisors and in lb2, I show ALL employees.
I then select SUP1 form lb1. lb2 now only shows EMP1 and EMP2 since they
"belong" to SUP1.

If I select SUP4 in lb1, I want lb2 to show EMP1, EMP2, SUP1 and SUP2,
since they are all SUP4's underlings.

Bonus question:

I want to be able to work with an unknown number of levels in the
employee-supervisor structure.
So I want to create a form with ... hm... lets say 5 listboxes (I can
figure out how to create those dynamically on my own...)
And then a listbox that shows the employees.
The first listbox, should show the top-level supervisor (those that do not
have a supervisor).
The second listbox should show all second-level supervisors, that is,
those who have a supervisor, but not those whose supervisor has a
supervisor.
And so on...
When you first open the form, all employees are shown in the listbox.
If you select a supervisor in any of the listboxes, the list is shortened.
If you select one of the upper-level supervisors both the other comboboxes
and the listbox should be shortened.

Any and all help appreciated,

/jim

[pietlinden]

Terry,
not to redirect the question, but is there any way to determine the
depth of a hierarchy with SQL?
The only way I could think of doing it was to use recordsets and update
them...:
1. find the person/persons with no supervisor (CEO, in your case), and
mark them
2. find all the people that report to him, and mark them
3. find all direct descendants of #2
Lather, rinse, repeat until you find everyone.

Of course, if you have staff reporting to more than one person, that's
a problem.

Thanks,
Pieter

[Mike Preston]

On 19 Dec 2005 14:04:12 -0800, pi********@hotmail.com wrote:

Terry,
not to redirect the question, but is there any way to determine the
depth of a hierarchy with SQL?
The only way I could think of doing it was to use recordsets and update
them...:
1. find the person/persons with no supervisor (CEO, in your case), and
mark them
2. find all the people that report to him, and mark them
3. find all direct descendants of #2
Lather, rinse, repeat until you find everyone.

Of course, if you have staff reporting to more than one person, that's
a problem.

I think he already indicated that you can set your query to be X
levels deep and the first query that returns 0 rows indicates that
whatever that level is minus 1 is the number of levels in the data.

mike

[Jim Andersen]

<pi********@hotmail.com> skrev i en meddelelse
news:11*********************@g14g2000cwa.googlegro ups.com...

Terry,
not to redirect the question, but is there any way to determine the
depth of a hierarchy with SQL?

The closest I have come is this sql-server function, it still needs some
work though, as you have to supply it with a mgrid. But you can call it
multiple times, for each mgr with no mgr, and see which has the deepest
tree:

CREATE FUNCTION dbo.ufn_GetSubtreeDepth
(
@mgrid AS int
)
RETURNS int
AS
BEGIN

RETURN CASE
WHEN EXISTS(SELECT * FROM Employees WHERE mgrid = @mgrid)
THEN 1 + (SELECT MAX(dbo.ufn_GetSubtreeDepth(empid))
FROM Employees
WHERE mgrid = @mgrid)
WHEN EXISTS(SELECT * FROM Employees WHERE empid = @mgrid)
THEN 1
ELSE NULL
END
END

/jim

[Terry Kreft]

Piet,
If you look at the second part of my post (after the line "The details of
filter for the display I'll leave to you.") I show a method for finding
this. It's not scientific but it will get you there in a reasonable length
of time.

The "proper" way to do it would probably be to create a B-Tree from the data
you can then work it out from that but I suspect that the method I showed
would be comparable in speed anyway.
--
Terry Kreft

<pi********@hotmail.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...

Terry,
not to redirect the question, but is there any way to determine the
depth of a hierarchy with SQL?
The only way I could think of doing it was to use recordsets and update
them...:
1. find the person/persons with no supervisor (CEO, in your case), and
mark them
2. find all the people that report to him, and mark them
3. find all direct descendants of #2
Lather, rinse, repeat until you find everyone.

Of course, if you have staff reporting to more than one person, that's
a problem.

Thanks,
Pieter

[pietlinden]

Terry,
I get it - you basically keep left joining tables until you get a
totally blank column. I did this the first time I tried to find a
solution to this.

maybe I'll do the "find person with no supervisor (i.e. the CEO), then
chase down the hierarchy until there are no employees left" thing.
(Well, when my very small brain is idle...)

Thanks for the pointer.
Pieter

[Terry Kreft]

Piet,
When you've gon one level higher than the number of levels in thehierarchy
you get an empty recordsset if you use the inner join example.

--
Terry Kreft

<pi********@hotmail.com> wrote in message
news:11**********************@g49g2000cwa.googlegr oups.com...

Terry,
I get it - you basically keep left joining tables until you get a
totally blank column. I did this the first time I tried to find a
solution to this.

maybe I'll do the "find person with no supervisor (i.e. the CEO), then
chase down the hierarchy until there are no employees left" thing.
(Well, when my very small brain is idle...)

Thanks for the pointer.
Pieter