Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL Recursion?? (Oracle 9i)
PL/SQL Recursion?? [message #279350] Wed, 07 November 2007 20:30 Go to next message
gviw
Messages: 2
Registered: November 2007
Junior Member
Hi,

I am not sure if recursion is the solution. But here is my issue.

I have a teacher and he has a class he is teaching. I want to know all the students who are taking that class only glitch is the students themselves might be taking some class and I want the students of the class the students are teaching.

i hope i am clear and not confusing you people

i have 2 relations

teacher-class table which has teacherid, classid
class-student table which has classid, studentid

in this say if teacher John teaches math and students A, B,C are enrolled in math.
in turn A might teach science , B might teach history. in the science class which A teaches there might be students E,F,G

So i want all the users if I pass John as teh parameter I should get
A,B,C,E,F,G...But I should not get John again if he is enrolled in A's science class

I am at loss now as to which way to go.
Can you please help.

I have modified my original requirement into a more readable example . pls let me know how to approach this issue. i am going nuts

thanks
J
Re: PL/SQL Recursion?? [message #279374 is a reply to message #279350] Thu, 08 November 2007 00:36 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I detect a homework assignment.

Build it up incrementally. Start with a SQL that just selects the teachers. Then add a join to get the classes they teach. Then add a join to get the students that take those classes.

Keep adding joins as you progress each step.

At the end, you will have all of the students you seek, but you may have duplicates where there are multiple paths. You can add a DISTINCT to eliminate duplicates.

Now that you have a baseline version that uses only joins, you can try a version that uses sub-queries to avoid the DISTINCT clause.

SELECT stuff
FROM   students
WHERE  class_id IN (
    SELECT class_id
    FROM   classes
    WHERE  teacher_id IN (
        SELECT ...
    )
)
...


Have a go - I'm not going to do your homework for you. If you get stuck, post what you have and ask for feedback.

Ross Leishman
Re: PL/SQL Recursion?? [message #279383 is a reply to message #279374] Thu, 08 November 2007 00:55 Go to previous messageGo to next message
gviw
Messages: 2
Registered: November 2007
Junior Member
Thanks rleishman for responding.

This is not a homework assignment. Its just that I have rephrased my requirement to a more simpler one that's easier to explain Smile

Anyway I am stuck at the place where I am joining the tables. Where would I stop the join . I keep joining the classes table to get the classes and get the students and find the classes taught by the students but where do I stop

This is where I was confused
. Thats when I thought may be I should call the procedure inside procedure. say this procedure is test should I call test within test and even in that case I need to know where I should stop.

Could you suggest me an idea that would be straightforward to implement?

Thanks
j
Re: PL/SQL Recursion?? [message #279386 is a reply to message #279374] Thu, 08 November 2007 01:06 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
rleishman wrote on Thu, 08 November 2007 17:36

If you get stuck, post what you have and ask for feedback.

Re: PL/SQL Recursion?? [message #279712 is a reply to message #279350] Fri, 09 November 2007 12:50 Go to previous messageGo to next message
Arun Srinath
Messages: 12
Registered: January 2005
Junior Member
I think there is a design issue here...
The best way to deal with this kiid of situationo is to use Oracle Hierarchical queries.

You could have table probable like this.

Teacher_id Student_id Class_id
1 11 100
1 12 100
1 13 100
11 14 101
12 15 102
13 16 103

Student in turn can be a teacher . in this way we are building a hierarchical structure.

You can write a simple query like below to get all the students under teacher_id = 1

select * from test_table
start with teacher_id=1
connect by teacher_id=prior student_id
order by teacher_id

Hope this is clear.

Thanks
Arun Srinath
Re: PL/SQL Recursion?? [message #279730 is a reply to message #279712] Fri, 09 November 2007 16:19 Go to previous message
Barbara Boehmer
Messages: 8632
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11g> SELECT * FROM teacher_class
  2  /

TEACHERID  CLASSID
---------- ----------
John       Math
A          Science
B          History

SCOTT@orcl_11g> SELECT * FROM class_student
  2  /

CLASSID    STUDENTID
---------- ----------
Math       A
Math       B
Math       C
Science    E
Science    F
Science    G
Science    John

7 rows selected.

-- 10g or 11g where nocycle is available:
SCOTT@orcl_11g> SELECT tc.teacherid, tc.classid, cs.studentid
  2  FROM   teacher_class tc, class_student cs
  3  WHERE  tc.classid = cs.classid
  4  START  WITH tc.teacherid = 'John'
  5  CONNECT BY NOCYCLE PRIOR cs.studentid = tc.teacherid
  6  		AND CONNECT_BY_ISCYCLE <> 1
  7  ORDER  BY LEVEL, teacherid, classid, studentid
  8  /

TEACHERID  CLASSID    STUDENTID
---------- ---------- ----------
John       Math       A
John       Math       B
John       Math       C
A          Science    E
A          Science    F
A          Science    G
A          Science    John

7 rows selected.

-- prior to 10g without nocycle:
SCOTT@orcl_11g> SELECT tc.teacherid, tc.classid, cs.studentid
  2  FROM   teacher_class tc, class_student cs
  3  WHERE  tc.classid = cs.classid
  4  START  WITH tc.teacherid = 'John'
  5  CONNECT BY PRIOR cs.studentid = tc.teacherid
  6  		AND PRIOR cs.studentid <> 'John'
  7  ORDER  BY LEVEL, teacherid, classid, studentid
  8  /

TEACHERID  CLASSID    STUDENTID
---------- ---------- ----------
John       Math       A
John       Math       B
John       Math       C
A          Science    E
A          Science    F
A          Science    G
A          Science    John

7 rows selected.

SCOTT@orcl_11g> 

Previous Topic: Extracting variable length string
Next Topic: order by in sql statement
Goto Forum:
  


Current Time: Mon Dec 05 20:58:09 CST 2016

Total time taken to generate the page: 0.14833 seconds