Query / SubQuery help URGENT!

[zombie_man23]

I have this query that needs to return info about a student. My problem is that I have some new requirements that I needed to add, and I placed them into subqueries under my Where clause(See problem area).

Example for first subquery: If a student from the primary query is in building 514,518,519,524 and their status = 'A', I want the query to return that student so I can get their info in the primary query. If the student is not in those buildings, or their status is not set to 'A' then I don't want the primary query to return the students information. Same concept with the other subqueries.

visual basic code:select
r.studentid,
initcap(trim(r.fname) || ' ' || trim(r.lname)),
initcap(trim(rb.building_name)),
case when t.am_location = '' then 'Walk' else t.am_location end location,
case when t.am_time = '' then '' else t.am_time end amtime,
case when t.pm_time = '' then '' else t.pm_time end pmtime,
initcap(trim(g.firstname) || ' ' || trim(g.lastname)),
initcap(trim(g.street)) || ' ' ||
case when g.apartment<>'' then 'Apt ' || g.apartment else '' end address,
initcap(trim(g.city) || ', ' || trim(g.state)),
case when length(g.zip) > 5 then g.zip[1,5] || '-' || g.zip[6,9] else g.zip end zip,
g.zip
from reg r, guardian g, family f, transportation t, regtb_building rb, regips ri
where
r.language = 'SP'
and
ri.rclanguage='Y'
and
r.status = 'A'
and
r.grade between '04' and '12'
and

----PROBLEM HERE
r.studentid = (select studentid from reg where building in (514,518,519,524) and status = 'A')
or
r.studentid = (select studentid from reg where building in (9420,9421) and status = 'A' and grade in (4,5,6,7))
or
r.studentid = (select r.studentid from reg r, regips re where r.building in (9420,9421) and r.grade in (8,9,10,11) and re.summersch = 'C' and re.summeresl = 'Y')
or
r.studentid = (select studentid from reg where building in (9715,9716,9717,9722,9723) and ri.summersch = 'C' and ri.summeresl = 'Y')
--END PROBLEM

and
r.building in (514,518,519,524,9420,9421,9715,9716,9717,9722,9723)
and
r.studentid = f.studentid
and
g.guardianid = f.guardianid
and
r.studentid = t.studentid
and
r.building = rb.building
and
ri.studentid = r.studentid
and
f.type = 'P'

[erickwidya]

i'm not quite understand for the code u post
can u post the table structure, value and what result u want to achieve instead?

[Zeb]

should it be?:r.studentid in (select studentid from reg where building in (514,518,519,524) and status = 'A')
or
r.studentid in (select studentid from reg where building in (9420,9421) and status = 'A' and grade in (4,5,6,7))
or
r.studentid in (select r.studentid from reg r, regips re where r.building in (9420,9421) and r.grade in (8,9,10,11) and re.summersch = 'C' and re.summeresl = 'Y')
or
r.studentid in (select studentid from reg where building in (9715,9716,9717,9722,9723) and ri.summersch = 'C' and ri.summeresl = 'Y')

[zombie_man23]

you were on the right track. i came up with that and then shrank it down to this

((r.building in (514,518,519,524,9420,9421) and r.status = 'A' and r.grade in ('04','05','06','07'))
or
(r.building in (9420,9421,9715,9716,9717,9722,9723) and r.grade in ('08','09','10','11') and (ri.summersch = 'C' or ri.summeresl = 'Y') and r.status = 'A'))


thanks for the help