Help with query

Suppose that I have a table that represents the chain-of-command hierarchy
of a company. Lets say that the first column of the table represents the
employee name and the other represents the employee's boss. Lets also assume
that one employee can have several bosses.
What I would like to do is to create some kind of query that will return the
company's Boss hierarchy. For example, lets pretend that my table has the
following information.
Worker Boss
-- --
JoeCacas Dumb
JoeCacas Dumber
Dumb MadDog
Dumber BigDaddy
Dumber BigCheese
BigDaddy Lordy
In this case, if I asked the query to tell me the Boss hierarchy of JoeCacas
I would expect the query to first search for all the bosses of JoeCacas
which will return the records:
-- 1 Level--
JoeCacas Dumb
JoeCacas Dumber
Then the query will need to search for the bosses of Dumb and Dumber which
will return the following:
-- 2 Level--
Dumb MadDog
Dumber BigDaddy
Dumber BigCheese
Finally, the query will return the bosses of MadDog, BidDaddy and BigCheese.
Since the only person that has a boss is BigDaddy then the result will be:
-- 3 Level--
BigDaddy Lordy
The result that I would expect would be:
JoeCacas Dumb
JoeCacas Dummber
Dumb MadDog
Dumber BigDaddy
Dumber BigCheese
BigDaddy Lordy
I think this can be done with some kind of recursive query. Is this
possible? Thank you.Hi Rene,
You can create a UDF to achieve the task.
Basically, iterate through the levels going upwards:
USE tempdb;
SET NOCOUNT ON;
GO
CREATE TABLE EmpsHier
(
worker VARCHAR(20) NOT NULL,
boss VARCHAR(20) NOT NULL,
PRIMARY KEY(worker, boss)
);
INSERT INTO EmpsHier VALUES('JoeCacas', 'Dumb');
INSERT INTO EmpsHier VALUES('JoeCacas', 'Dumber');
INSERT INTO EmpsHier VALUES('Dumb', 'MadDog');
INSERT INTO EmpsHier VALUES('Dumber', 'BigDaddy');
INSERT INTO EmpsHier VALUES('Dumber', 'BigCheese');
INSERT INTO EmpsHier VALUES('BigDaddy', 'Lordy');
GO
CREATE FUNCTION fn_mgmtchain(@.emp AS VARCHAR(20)) RETURNS
@.MgmtChain TABLE
(
worker VARCHAR(20),
boss VARCHAR(20),
lvl INT
)
AS
BEGIN
DECLARE @.lvl AS INT;
SET @.lvl = 0;
INSERT INTO @.MgmtChain
SELECT worker, boss, @.lvl FROM EmpsHier
WHERE worker = @.emp;
WHILE @.@.rowcount > 0
BEGIN
SET @.lvl = @.lvl + 1;
INSERT INTO @.MgmtChain
SELECT P.worker, P.boss, @.lvl
FROM EmpsHier AS P
JOIN @.MgmtChain AS C
ON C.lvl = @.lvl - 1
AND C.boss = P.worker;
END
RETURN;
END
GO
SELECT * FROM fn_mgmtchain('JoeCacas');
worker boss lvl
-- -- --
JoeCacas Dumb 0
JoeCacas Dumber 0
Dumb MadDog 1
Dumber BigCheese 1
Dumber BigDaddy 1
BigDaddy Lordy 2
BG, SQL Server MVP
www.SolidQualityLearning.com
"Rene" <nospam@.nospam.com> wrote in message
news:eeo3WsMYFHA.580@.TK2MSFTNGP15.phx.gbl...
> Suppose that I have a table that represents the chain-of-command hierarchy
> of a company. Lets say that the first column of the table represents the
> employee name and the other represents the employee's boss. Lets also
> assume that one employee can have several bosses.
> What I would like to do is to create some kind of query that will return
> the company's Boss hierarchy. For example, lets pretend that my table has
> the following information.
> Worker Boss
> -- --
> JoeCacas Dumb
> JoeCacas Dumber
> Dumb MadDog
> Dumber BigDaddy
> Dumber BigCheese
> BigDaddy Lordy
>
> In this case, if I asked the query to tell me the Boss hierarchy of
> JoeCacas
> I would expect the query to first search for all the bosses of JoeCacas
> which will return the records:
> -- 1 Level--
> JoeCacas Dumb
> JoeCacas Dumber
> Then the query will need to search for the bosses of Dumb and Dumber which
> will return the following:
> -- 2 Level--
> Dumb MadDog
> Dumber BigDaddy
> Dumber BigCheese
> Finally, the query will return the bosses of MadDog, BidDaddy and
> BigCheese.
> Since the only person that has a boss is BigDaddy then the result will be:
> -- 3 Level--
> BigDaddy Lordy
>
> The result that I would expect would be:
> JoeCacas Dumb
> JoeCacas Dummber
> Dumb MadDog
> Dumber BigDaddy
> Dumber BigCheese
> BigDaddy Lordy
> I think this can be done with some kind of recursive query. Is this
> possible? Thank you.
>|||Hi
you can also check for this:
http://support.microsoft.com/defaul...kb;en-us;248915
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Itzik Ben-Gan" wrote:

> Hi Rene,
> You can create a UDF to achieve the task.
> Basically, iterate through the levels going upwards:
> USE tempdb;
> SET NOCOUNT ON;
> GO
> CREATE TABLE EmpsHier
> (
> worker VARCHAR(20) NOT NULL,
> boss VARCHAR(20) NOT NULL,
> PRIMARY KEY(worker, boss)
> );
> INSERT INTO EmpsHier VALUES('JoeCacas', 'Dumb');
> INSERT INTO EmpsHier VALUES('JoeCacas', 'Dumber');
> INSERT INTO EmpsHier VALUES('Dumb', 'MadDog');
> INSERT INTO EmpsHier VALUES('Dumber', 'BigDaddy');
> INSERT INTO EmpsHier VALUES('Dumber', 'BigCheese');
> INSERT INTO EmpsHier VALUES('BigDaddy', 'Lordy');
> GO
> CREATE FUNCTION fn_mgmtchain(@.emp AS VARCHAR(20)) RETURNS
> @.MgmtChain TABLE
> (
> worker VARCHAR(20),
> boss VARCHAR(20),
> lvl INT
> )
> AS
> BEGIN
> DECLARE @.lvl AS INT;
> SET @.lvl = 0;
> INSERT INTO @.MgmtChain
> SELECT worker, boss, @.lvl FROM EmpsHier
> WHERE worker = @.emp;
> WHILE @.@.rowcount > 0
> BEGIN
> SET @.lvl = @.lvl + 1;
> INSERT INTO @.MgmtChain
> SELECT P.worker, P.boss, @.lvl
> FROM EmpsHier AS P
> JOIN @.MgmtChain AS C
> ON C.lvl = @.lvl - 1
> AND C.boss = P.worker;
> END
> RETURN;
> END
> GO
> SELECT * FROM fn_mgmtchain('JoeCacas');
> worker boss lvl
> -- -- --
> JoeCacas Dumb 0
> JoeCacas Dumber 0
> Dumb MadDog 1
> Dumber BigCheese 1
> Dumber BigDaddy 1
> BigDaddy Lordy 2
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Rene" <nospam@.nospam.com> wrote in message
> news:eeo3WsMYFHA.580@.TK2MSFTNGP15.phx.gbl...
>
>|||Chandra,
The example you provided expands/explodes a node (returning descendants down
the digraph). Rene asked for ancestors (up the digraph).
BG, SQL Server MVP
www.SolidQualityLearning.com
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:7E732409-B2C7-4B00-B986-3A85B5098E65@.microsoft.com...
> Hi
> you can also check for this:
> http://support.microsoft.com/defaul...kb;en-us;248915
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Itzik Ben-Gan" wrote:
>|||Thanks Itsik, that was awesome! I pretty much copied and pasted your code
and everything worked great.
It took me a little bit to figure out what you did but now I understand it.
Thanks again.
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:uiI%23HwNYFHA.252@.TK2MSFTNGP12.phx.gbl...
> Chandra,
> The example you provided expands/explodes a node (returning descendants
> down the digraph). Rene asked for ancestors (up the digraph).
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Chandra" <chandra@.discussions.microsoft.com> wrote in message
> news:7E732409-B2C7-4B00-B986-3A85B5098E65@.microsoft.com...
>|||I was just thinking. What if multiple users call the function at the same
time? Will they all be modifying the same table at the same time screwing up
the result? If this is the case, is there a way to create a unique table
every time the function is called?
"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:%23BtRqiNYFHA.2288@.TK2MSFTNGP14.phx.gbl...
> Hi Rene,
> You can create a UDF to achieve the task.
> Basically, iterate through the levels going upwards:
> USE tempdb;
> SET NOCOUNT ON;
> GO
> CREATE TABLE EmpsHier
> (
> worker VARCHAR(20) NOT NULL,
> boss VARCHAR(20) NOT NULL,
> PRIMARY KEY(worker, boss)
> );
> INSERT INTO EmpsHier VALUES('JoeCacas', 'Dumb');
> INSERT INTO EmpsHier VALUES('JoeCacas', 'Dumber');
> INSERT INTO EmpsHier VALUES('Dumb', 'MadDog');
> INSERT INTO EmpsHier VALUES('Dumber', 'BigDaddy');
> INSERT INTO EmpsHier VALUES('Dumber', 'BigCheese');
> INSERT INTO EmpsHier VALUES('BigDaddy', 'Lordy');
> GO
> CREATE FUNCTION fn_mgmtchain(@.emp AS VARCHAR(20)) RETURNS
> @.MgmtChain TABLE
> (
> worker VARCHAR(20),
> boss VARCHAR(20),
> lvl INT
> )
> AS
> BEGIN
> DECLARE @.lvl AS INT;
> SET @.lvl = 0;
> INSERT INTO @.MgmtChain
> SELECT worker, boss, @.lvl FROM EmpsHier
> WHERE worker = @.emp;
> WHILE @.@.rowcount > 0
> BEGIN
> SET @.lvl = @.lvl + 1;
> INSERT INTO @.MgmtChain
> SELECT P.worker, P.boss, @.lvl
> FROM EmpsHier AS P
> JOIN @.MgmtChain AS C
> ON C.lvl = @.lvl - 1
> AND C.boss = P.worker;
> END
> RETURN;
> END
> GO
> SELECT * FROM fn_mgmtchain('JoeCacas');
> worker boss lvl
> -- -- --
> JoeCacas Dumb 0
> JoeCacas Dumber 0
> Dumb MadDog 1
> Dumber BigCheese 1
> Dumber BigDaddy 1
> BigDaddy Lordy 2
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Rene" <nospam@.nospam.com> wrote in message
> news:eeo3WsMYFHA.580@.TK2MSFTNGP15.phx.gbl...
>|||I am assuming that the @.MgmtChain TABLE is created on the tempDB database,
although I have a feeling tempDB is not used at all here. Just checking.
"Rene" <nospam@.nospam.com> wrote in message
news:OGy$fTXYFHA.3280@.TK2MSFTNGP09.phx.gbl...
>I was just thinking. What if multiple users call the function at the same
>time? Will they all be modifying the same table at the same time screwing
>up the result? If this is the case, is there a way to create a unique table
>every time the function is called?
>
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> message news:%23BtRqiNYFHA.2288@.TK2MSFTNGP14.phx.gbl...
>|||>> the chain-of-command hierarchy of a company. Lets say that the first colu
mn of the table represents the employee name and the other represents the em
ployee's boss. Lets also assume that one employee can have several bosses. <
<
Several bosses? Isn' t that like having a circle has corners? Look
up the definition of a hierarchy. If you really wanted a hierarchy,
then get a copy of TREES & HIERARCHIES IN SQL for several
non-procedural, non-recursive ways to model this which will run orders
of magnitude faster than a recursive proc or a loop.|||> Several bosses? Isn' t that like having a circle has corners?
I don't have such table. It was just an example (analogy) that I came up
with to try to make it easier to get my point across. Hierarchy may not be
the most proper word to use but it I figured it would help explain what I
needed.|||The table is logically a variable local to the batch. No conflicts with
other invocations.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Rene" <nospam@.nospam.com> wrote in message
news:OGy$fTXYFHA.3280@.TK2MSFTNGP09.phx.gbl...
>I was just thinking. What if multiple users call the function at the same
>time? Will they all be modifying the same table at the same time screwing
>up the result? If this is the case, is there a way to create a unique table
>every time the function is called?
>
> "Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in
> message news:%23BtRqiNYFHA.2288@.TK2MSFTNGP14.phx.gbl...
>