Determining the organizational hierarchy of users using a single query

Question

This was a question which I was asked in an interview recently:

You have various types of users in an organization : Junior (is bossed by) Supervisor (is bossed by) Manager (is bossed by) CEO.

We agreed upon this single simplified table schema. Users : {userId, userName, UserType(J, S, M, C), bossUserId}

Q : Write a single query to determine the complete organizational hierarchy of a Junior employee with userId = 11.

Sample Data :

enter image description here

The answer would be => PQR2 --> GHI2 --> DEF1 --> ABC1

This was my solution :

select e1.userName, e2.userName, e3.userName, e4.userName from 
abc e1 inner join users e2 on e1.bossUserId = e2.userId
inner join users e3 on e2.bossUserId = e3.userId
inner join users e4 on e3.bossUserId = e4.userId
where e1.userId = 11;

I do realize that self joining 4 times is horrible, but I was not able to think of anything else. The interviewer told me that there is a better way and that the data will show up columnwise. (using a maximum of 2 self joins, if at all)

Another possibility was to write a stored procedure, but again it is not a single query.

Can anyone help me with this?

It will be better if you once go through http://stackoverflow.com/questions/11064913/achieve-hierarchy-in-a-less-number-of-mysql-queries It will be of great help to you — Sashi Kant, Nov 28 '12 at 14:24
Was the question specifically for MYSQL, or for a general SQL variant? — podiluska, Nov 28 '12 at 14:28
@TJ- MySQL doesn't have recursive queries. With an association list you'll never get four tiers of the hierarchy with a single join. I think that either the interviewer bluffed you, or it's not for MySQL *(Oracle, PostGreSQL, MS-SQL-Server, etc, could all do this with a recursive query.)* — MatBailie, Nov 28 '12 at 14:30
SashiKant Thank you. I will have a look. @podiluska I assume so. I told him I have experience in working with mySQL and then he asked this question. But non-mySql solution would also help me. — TJ-, Nov 28 '12 at 14:30
@Dems I see. I guess, then he was not talking about mySql, but just relational dbs at a conceptual level. — TJ-, Nov 28 '12 at 14:32
@TJ- A strong question to have asked the interviewer would have been `Which RDBMS or which ANSI-SQL standard? What are the constraints to this question?` — MatBailie, Nov 28 '12 at 14:34

score 1 · Accepted Answer · answered Nov 28 '12 at 14:41

Following your comments, in MS SQL (and others, similarly) you can do this

;with cte as (
    select *, 0 as level from yourtable
    union all 
    select cte.id, t2.name, t2.ut, t2.bossid, level+1
    from cte
        inner join yourtable t2 on cte.bossid = t2.id
)
    select name, ut from cte
    where id=11
    order by level

which gives you

pqr2    j
ghi2    s
def1    m
abc1    c

But MySQL doesn't support this construct.

Determining the organizational hierarchy of users using a single query

1 Answers1