SELECT Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SELECT Query

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2009-04-09 : 00:40:01

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

ayamas
Aged Yak Warrior

552 Posts

Posted - 2009-04-09 : 02:47:03

But in the first level how do you define "range".For example in table grades for orgid 431 the gradeid is 10 & 15 but in collection test the gradeid is 12 for facility code 431.The should 431 should also be selected?

kunal.mehta
Yak Posting Veteran

83 Posts

Posted - 2009-04-09 : 02:56:21

Hi,
try this

SELECT
a.CoolectionID,
a.dmid,
a.gradeid,
a.facilitycode,o.instid
FROM collection_test a,organization o,grades g
where a.gradeid=g.gradeid and o.orgid=g.orgid and
not exists (
select 1 from Grades where orgid=a.facilitycode
and a.gradeid in (select gradeid from grades where orgid = a.facilitycode )
)
and o.instid not in ('21','41','61','82')
group by a.CoolectionID,
a.dmid,
a.gradeid,
a.facilitycode,o.instid

Kunal

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2009-04-09 : 05:46:26

Hey Guys,

I got the answer. This is the correct one.

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

Thanks so much!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources