April 8, 2009 at 10:46 pm
Dear Friends,
I need small help.
I have 3 tables Grades,CollectionTest and Oraganization.
Taking Collection_Test into consideration, i need display invalid records from it.
ForeignKeys
---------------
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
(orgid int,
gradeid int
)
insert into grades
select 430,1
union all
select 430,2
union all
select 430,3
union all
select 430,4
union all
select 430,5
union all
select 430,6
union all
select 430,7
union all
select 430,8
union all
select 430,9
union all
select 430,10
union all
select 431,11
union all
select 431,11
create table collection_test
(CoolectionID int,
dmid int,
gradeid int,
facilitycode int
)
insert into collection_test
select 5,50,5,430
union all
select 5,50,5,431
create table organization
(orgid int,
instid int,
district int
)
insert into organization
select 430,21,799
union all
select 430,41,800
union all
select 430,61,400
union all
select 430,82,500
union all
select 430,11,900
union all
select 431,21,500
union all
select 431,41,500
SELECT
a.CoolectionID,
a.dmid,
a.gradeid,
a.facilitycode
FROM collection_test a
where
not exists (
select 1 from Grades where orgid=a.facilitycode
and a.gradeid in (select gradeid from grades where orgid = a.facilitycode )
)
April 9, 2009 at 3:48 am
I got the answer
SELECT
a.CoolectionID,
a.dmid,
a.gradeid,
a.facilitycode
FROM collection_test a
where
not exists (
select 1 from Grades where orgid=a.facilitycode
and a.gradeid in (select gradeid from grades where orgid = a.facilitycode )
)
and not exists
(select 1 from organization c
where c.orgid = a.facilitycode
and instid IN (21,41,61,82)
)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply