loop in mysql query

[venkyb47]

I have a table EmpList. I want to use loop to bring result as I expected. Example,

EmpNo   ReportingTo
-------------------
27      14
68      14
104     27
105     27
138     27
139     68
150     8
151     8
8       5

Suppose query input is 14, I need result like this

EmpNo
------
27
68
104
105
138
139

Here 27,68 reportingto 14 and 104,105,138 reportingto 27 and 139 reportingto 68.
I am trying to get result using query like this
SELECT EmpNo FROM EmpList where ReportingTo IN (SELECT DISTINCT EmpNo FROM EmpList where ReportingTo = 14);
But this query gives only one level result.

[Reverend Jim]

Can you describe what you want in words? There are several ways to generate the given results. One is

SELECT EmpNo FROM EmpList WHERE ReportingTo >= 14

Unless you can tell us what you want it is impossible to give a meaningful answer.

[venkyb47]

I am giving EmpNo as input to the query(ex: 14). I am expecting who are all reportingto EmpNo14(ex:27,68 from table) and along with it subreportees if any(EmpNo who are all reporting to 27 and 68(ex:104,105,138 reportingto 27 and 139 reportingto 68)). Finally I am trying to get result in a single column like

EmpNo
-----
27
68
104
105
138
139

[pritaeas]

This is a recursive tree traversal. You cannot do this in a single query. If you search this topic you find some solutions, but most use a couple of stored procedures.

[ryanjayson]

Try this one

SELECT EmpNo FROM EmpList where ReportingTo IN (SELECT DISTINCT EmpNo FROM EmpList where ReportingTo IN (SELECT DISTINCT ReportingTo FROM EmpList));

[dev90]

SELECT EMPNO FROM EMPLIST WHERE REPORTINGTO=14 OR
REPORTING TO IN (SELECT EMPNO FROM EMPLIST WHERE REPORTINGTO=14);

THIS GIVES EXACTLY WHAT YOU WANT. :)

[rch1231]

A couple of things you have to determine first such as how far down the tree are you going to go. Meaning Is there a maximun munber of reporting levels that you intend to display in the result (such as up to 5 levels deep or 18 reports to 22 reports to 55 reports to 77 reports to 14 and you will not show anybody reporting to 18) other wise you are going to have a lot of coding.