Results 1 to 3 of 3
  1. #1
    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:

    Headmaster
    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. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    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. #3
    Join Date
    Jul 2014
    Posts
    6
    Quote Originally Posted by MCrowley View Post
    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
  •