2

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

skaffman
  • 398,947
  • 96
  • 818
  • 769
sagar
  • 21
  • 1
  • 3
  • I.e. you want to have 4,5,8,9,10,11,12,13, right? – phimuemue Oct 22 '10 at 09:14
  • Maybe this could help you: http://stackoverflow.com/questions/53108/is-it-possible-to-make-a-recursive-sql-query – phimuemue Oct 22 '10 at 09:16
  • possible duplicate of [Sql Server2005 query problem](http://stackoverflow.com/questions/3993927/sql-server2005-query-problem) –  Oct 22 '10 at 13:03

1 Answers1

3

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.

Erwin Smout
  • 18,113
  • 4
  • 33
  • 52