I have 3 tables Grades,CollectionTest and Oraganization.
Taking Collection_Test into consideration, i need display invalid records from it.
collection_test(facilitycode) refers to Grades(orgid)
and one more thing is that there is no relationship between collection_test and Organization tables but logically collection_test(facilitycode) refers to Oraganization(orgid).
Condition 1 : Take each record from "Collection_test" table,consider facilitycode,gradeid fields. For that facility code u need to take corressponding gradeid and lookup into "Grades" table and check
for that facilitycode/orgid , the grade is falling within the range or not.
Example, if i consider 1 st record in Collection_test table 430 is my faciltycode and 5 is my gradeid. Now i need to lookup into Grades table for orgid 430 and get the corresponding gradeids from the "Grades" table.
(i.e for 430 i will get grades from 1,2,3,4,5,6,7,8,9,10) and check whether collection_test(gradeid) i.e 5 is falling within 1,2,3,4,5,6,7,8,9,10 range or not. If not it is an invalid record and i need to display it.
Again we have an Exception here, Eventhough the grade is not falling within the range but taking into "Organization" table into consideration ( this is second level condition checking) check for the orgid in this table(
collection_test(facilitycode) refers to Oraganization(orgid)) and for that organization id, if the institution code (instid) is IN (21,41,61,82) then i can ignore that record i.e it is no more an INVALID record and is not displayed in the output.
So as a result if u take 2nd record into consideration, i.e 5 431.Though it fails in the first condition but it pass's in the second level conditon and finally no rows is selected for the output.
I tried till the first level of the condition , but got stuck for the second level condition.
Please help me out.
Thanks in Advance.
create table Grades
insert into grades
insert into collection_test
create table organization
insert into organization
FROM collection_test a
not exists (
select 1 from Grades where orgid=a.facilitycode
and a.gradeid in (select gradeid from grades where orgid = a.facilitycode )
FROM collection_test a
where not exists
(select 1 from Grades b
) and not exists
(select 1 from organization c
where c.orgid = a.facilitycode
and instid IN (21,41,61,82)