Link to home
Start Free TrialLog in
Avatar of Member_2_1242703
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?
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

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.
Avatar of Member_2_1242703
Member_2_1242703

ASKER

Thanks. Hopefully this is sufficient?
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
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
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Works against the table we created but running it against a table of just 5600 records I got:

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.