# Thread: Recursive code to create list

1. Registered User
Join Date
Jul 2014
Posts
6

## Unanswered: Recursive code to create list

Hey guys

I wasnt really sure if the thread title was great, but here's the problem.

What I need?: A way of dynamically inserting a list in the WHERE clause of a query. (in the form, WHERE ID = 1,2,3,6,9 etc)

Imagine an example DB with 3 columns Student ID, Name, Teacher_ID. (Lets assume Teacher_ID with value 100 means its the Headmaster)

I need to create a list with Student ID's, who are directly/indirectly under the Headmaster. Example:

Teacher 1 (ID 200)
Teacher 2 (ID 250)
Student 1 (ID 300)
Director
Teacher 4
Student 5

In the above example, since I only want those students/teachers under the headmaster, either directly/indirectly, my list would contain Teacher 1, Teacher 2, and Student 1. (In my case, just their ID's, so 200, 250, 300)

Director, Teacher 4 and Teacher 5 wouldnt be in the list since theyre not directly/indirectly Headmaster.

Hope I explained that well.
If you have any questions let me know

Thanks!

2. Registered User
Join Date
Jan 2003
Location
Massachusetts
Posts
5,858
My first thought is that you will be looking for a recursive CTE (Common Table Expression) to generate a hierarchy, but I am not sure how you are defining directly or indirectly under. Is the relationship between the headmaster and the teachers/students kept in a table somewhere?

3. Registered User
Join Date
Jul 2014
Posts
6
Originally Posted by MCrowley
My first thought is that you will be looking for a recursive CTE (Common Table Expression) to generate a hierarchy, but I am not sure how you are defining directly or indirectly under. Is the relationship between the headmaster and the teachers/students kept in a table somewhere?
Yep, all of it is in one table.
Every row has a Student ID, a Name and a Teacher ID.
So basically I need to generate a list (which will then be used in the WHERE clause of another query) of the student IDs who are directly/indirectly under some teacher ID.

i.e. if I say I want everyone under Teacher ID 50, then a new list should be created with multiple student ID´s (like: 4,8,12,14,45) who are directly/indirectly related to Teacher ID 50.

I hope that explained it a bit better

Thanks!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•