Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2007
    Posts
    41

    Unanswered: Select Query help!

    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 )
    )

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Code:
    SELECT 
     a.CoolectionID
    ,a.dmid
    ,a.gradeid
    ,a.facilitycode
    FROM collection_test a
    where not exists 
    (select 1 from Grades b 
     where b.orgid=a.facilitycode 
       and b.gradeid=a.gradeid
    ) and not exists 
    (select 1 from organization c 
     where c.orgid = a.facilitycode
       and instid IN (21,41,61,82)
    )

  3. #3
    Join Date
    Nov 2007
    Posts
    41
    Thanks so much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •