Member_2_1242703
asked on
SQL SELECT query help
I have the following table:
ID NAME MGRID
8755 HARD HARRY 0000
8756 PETER GRIFFIN 8755
8757 LOIS GRIFFIN 8756
8758 CLEVELAND BROWN 8756
9999 GLEN QUAGMIRE 8756
8759 ZACK MORRIS 8761
8760 AC SLATER 8761
8761 RICHARD BELDING 8755
8762 JESSIE SPANNO 8761
8763 LISA TURTLE 8759
8764 SCREECH POWERS 8759
What I'm trying to do is pull the name field for every record in which MGRID is a given value and NAME for every record in which MGRID is the ID of a record already pulled.
For example, if the given value was 8755, the entire list above would be the result. If the given value was 8761 (Richard Belding,) then the result would be:
8759 ZACK MORRIS 8761
8760 AC SLATER 8761
8761 RICHARD BELDING 8755
8762 JESSIE SPANNO 8761
8763 LISA TURTLE 8759
8764 SCREECH POWERS 8759
How do I do this?
ID NAME MGRID
8755 HARD HARRY 0000
8756 PETER GRIFFIN 8755
8757 LOIS GRIFFIN 8756
8758 CLEVELAND BROWN 8756
9999 GLEN QUAGMIRE 8756
8759 ZACK MORRIS 8761
8760 AC SLATER 8761
8761 RICHARD BELDING 8755
8762 JESSIE SPANNO 8761
8763 LISA TURTLE 8759
8764 SCREECH POWERS 8759
What I'm trying to do is pull the name field for every record in which MGRID is a given value and NAME for every record in which MGRID is the ID of a record already pulled.
For example, if the given value was 8755, the entire list above would be the result. If the given value was 8761 (Richard Belding,) then the result would be:
8759 ZACK MORRIS 8761
8760 AC SLATER 8761
8761 RICHARD BELDING 8755
8762 JESSIE SPANNO 8761
8763 LISA TURTLE 8759
8764 SCREECH POWERS 8759
How do I do this?
A recursive query should handle this. If you'll post directly useable data -- CREATE TABLE and INSERT statements for the data above -- I'll take a crack at writing the SQL.
ASKER
Thanks. Hopefully this is sufficient?
EEtest.sql
EEtest.sql
Interesting query.
DECLARE @MGRID varchar(10);
SET @MGRID = '8761'
;WITH CTE_MGR_LOOKUP AS (
SELECT ID /*, 1 AS level*/
FROM EMPLOYEE_TEST
WHERE ID = @MGRID
UNION ALL
SELECT et.ID /*, level + 1 AS level*/
FROM CTE_MGR_LOOKUP cml
INNER JOIN EMPLOYEE_TEST et ON et.MGRID = cml.ID
)
SELECT ID /*, level*/
FROM CTE_MGR_LOOKUP
ORDER BY ID
DECLARE @MGRID varchar(10);
SET @MGRID = '8761'
;WITH CTE_MGR_LOOKUP AS (
SELECT ID /*, 1 AS level*/
FROM EMPLOYEE_TEST
WHERE ID = @MGRID
UNION ALL
SELECT et.ID /*, level + 1 AS level*/
FROM CTE_MGR_LOOKUP cml
INNER JOIN EMPLOYEE_TEST et ON et.MGRID = cml.ID
)
SELECT ID /*, level*/
FROM CTE_MGR_LOOKUP
ORDER BY ID
ASKER
I got what you have working, displaying ID but unable to get the NAME as well. I changed the column name of NAME to EMNAME just in case. Still no luck.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Works against the table we created but running it against a table of just 5600 records I got:
I added the following to the end of the query:
option (maxrecursion 0)
I let it run for a couple minutes before cancelling. Any ideas?
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.
I added the following to the end of the query:
option (maxrecursion 0)
I let it run for a couple minutes before cancelling. Any ideas?
Hmm, you must an ID / MGRID loop in your data. For example, EMP 2 managers EMP 1 who manages EMP 3 who manages EMP 2, a never-ending recursion.
In that case, we'd have to use looping code to work up the manager chain, so that we can drop out any already-found manager ids.
In that case, we'd have to use looping code to work up the manager chain, so that we can drop out any already-found manager ids.