Recursive query

Question

I have a table which contains the following fields

• Supervisorid
• Empid

This is just like a referral program. A guy can refer 3 guys under him i.e, 3 is referring three guys namely 4 5 8 similarly 4 is referring 9 10 and 11 likewise 8 is referring 12, 13 it goes like this..

I want a query to get all EmpId under Supervisor 3

Answer 1

I think it's the best approach of recursive queries : http://kamfonas.com/id3.html

Answer 2

Do you want us to write the solution for you, or explain a bit how recursive queries can be built up ?

An example of how they are built up is on http://publib.boulder.ibm.com/infocenter/db2luw/v8//topic/com.ibm.db2.udb.doc/ad/samples/clp/s-flt-db2.htm.

The IBM DB2 redbook has an entire chapter on SQL recursion.

The gist is that the following steps are generally involved:

• you define a "seed". SELECT SUPID, EMPID, 1 AS LVL FROM EMP WHERE SUPID = 3;

• you assign to this a name. WITH SRC AS <your seed here>

• you define the way to go to the 'next level', starting from the seed, using the assigned name. SELECT SRC.SUPID, F.EMPID, SRC.LVL+1 FROM SRC, EMP WHERE SRC.EMPID=EMP.SUPID

• you combine the two together (inside the WITH clause) WITH SRC AS <your seed here> UNION ALL <the other SELECT here>

• (optionally) you define which columns to select. SELECT EMPID, LVL FROM SRC.