I have two tables:


drop table prereqs
/
 create table prereqs
 (
 course_seq_no number(4),
 pre_req number(4),
 prtype varchar2(7)
 )
/




drop table src;

create table src
(
reg_no number(8),
course_seq_no number(4),
semester_code number(1),
semester_year number(4),
gradecharvalue varchar2(2)
)
/



---with sample data




 insert into prereqs values(169,244,'PassAll');
 insert into prereqs values(169,160,'PassAll');
 insert into prereqs values(96,277,'PassAny');
 insert into prereqs values(96,643,'PassAny');



insert into src values(2013192,277,3,2010,'B');
insert into src values(2013192,148,3,2010,'C+');
insert into src values(2013192,244,3,2010,'B-');
insert into src values(2013192,1,3,2010,'B+');
insert into src values(2013192,367,3,2010,'D');
insert into src values(2013192,155,3,2010,'A-');
insert into src values(2013192,3,3,2010,'C+');
insert into src values(2013192,242,3,2010,'C+');
insert into src values(2013192,5,1,2011,'C');
insert into src values(2013192,156,1,2011,'B-');
insert into src values(2013192,250,1,2011,'B+');
insert into src values(2013192,248,1,2011,'C+');
insert into src values(2013192,149,1,2011,'C+');
insert into src values(2013192,243,1,2011,'W');
insert into src values(2013192,162,2,2011,'C');
insert into src values(2013192,205,1,2011,'B-');
insert into src values(2013192,159,3,2011,'F');
insert into src values(2013192,150,3,2011,'B+');
insert into src values(2013192,64,3,2011,'C');
insert into src values(2013192,66,3,2011,'B-');
insert into src values(2013192,244,3,2011,'D+');
insert into src values(2013192,164,3,2011,'C+');
insert into src values(2013192,170,3,2011,'C-');
insert into src values(2013192,322,1,2011,'B-');
insert into src values(2013192,243,2,2011,'D+');
insert into src values(2013192,165,1,2012,'B-');
insert into src values(2013192,152,1,2012,'C-');
insert into src values(2013192,68,1,2012,'B-');
insert into src values(2013192,70,1,2012,'B-');
insert into src values(2013192,664,1,2012,'B-');
insert into src values(2013192,158,1,2012,'C');
insert into src values(2013192,126,3,2012,'');
insert into src values(2013192,151,3,2012,'');
insert into src values(2013192,159,3,2012,'');
insert into src values(2013192,180,3,2012,'');
insert into src values(2013192,666,3,2012,'');
insert into src values(2013192,172,3,2012,'');
insert into src values(2013192,169,3,2012,'');

insert into src values(2013200,96,3,2012,'');
insert into src values(2013200,277,1,2012,'F');

-------------------------------------------------------


tables used:

SRC: Course registration table
prereqs: pre requisit table


Key: course_seq_no


Rules:

Students register their courses in SRC. its pre requisit are present in table prereqs.
Pre requisits are of two types. PassAll and PassAny.

1 if PassAll then all pre requisits should be passed. (Pass means not having F grade) 

example:

FOR COURSE_SEQ_NO 169 there are two pre requisits 244 and 160.

This means that for regno 2013192, course 169, two courses ie. 244 and 160 should exist in SRC
and both must not have gradecharvalue 'F' in last regisered semester.


2 if PassAny then any prerequisit should be passed. (Pass means not having F grade) 


example:

FOR COURSE_SEQ_NO 96 there are two pre requisits 277 and 643.

This means that for regno 2013192, course 96, ANY OF THESE TWO pre_reqs ie. 277 and 643
should exist in SRC
and AT LEAST ONE must HAVE gradecharvalue <> 'F' in ANY regisered semester.


Question:


Now I have to find out what regnos have voilated the rules.

ie.


regno	course_seq_no	pre_req		remarks
2013192	169		160		(not registerd)
2013200	96		277		F

	
 

SQL> select v1.*,v2.*
  2  from
  3  (select s.reg_no,s.course_seq_no as seq1,p.*
  4  from src s, prereqs p
  5  where s.course_seq_no=p.course_seq_no
  6  and semester_code=3 
  7  and semester_year=2012
  8  and reg_no=2013192
  9  ) v1,
 10  (select reg_no,course_seq_no,gradecharvalue
 11  ,semester_code,semester_year
 12   from src
 13  where course_seq_no in
 14  (select pre_req from prereqs)
 15  and reg_no in
 16  (select distinct reg_no from src
 17  where semester_code=3 and semester_year=2012
 18  and reg_no not like '2012%'
 19  and reg_no=2013192)
 20  )v2
 21  where v1.pre_req=v2.course_seq_no(+)
 22  /

    REG_NO       SEQ1 COURSE_SEQ_NO    PRE_REQ PRTYPE      REG_NO COURSE_SEQ_NO GR SEMESTER_CODE SEMESTER_Y
---------- ---------- ------------- ---------- ------- ---------- ------------- -- ------------- ---
   2013192        169           169        244 PassAll    2013192           244 B-             3          2010
   2013192        169           169        244 PassAll    2013192           244 D+             3          2011
   2013192        169           169        160 PassAll