Real puzzler !!??!!

From: Eric C. Janzen <ejanzen_at_telusplanet.net>
Date: Fri, 25 Feb 2000 22:11:24 GMT
Message-ID: <g6Dt4.8875$pn1.312871_at_news0.telusplanet.net>

[Quoted] [Quoted] I am writing a report for a client. The report has to provide a list of [Quoted] schools. The schools table has four flags as follows:

KINDERGARTEN_FLAG
ELEMENTARY_FLAG
JUNIOR_HIGH_FLAG
HIGH_SCHOOL_FLAG If the user wants to get a list of schools that teach kindergarten, they [Quoted] would check a forms checkbox that says "Include kindergarten". There will [Quoted] be checkboxes for all the other grades as well. So I then pass the values [Quoted] of these checkboxes to a report.

Lets say I have the following rows:

   K E J H

1.Y N N N
2. Y N N Y
3. N N N Y
4. Y Y N N
5. N Y Y N
6. N N Y N
7. Y N Y N

If they want Kindergarten and High school, they would get rows 1,2,3,4,7 [Quoted] If they want junior high, they would get 5,6,7 If they want elementary and junior high, they would get 4,5,6,7

I can easily write a query for each scenario above using OR, but how do I [Quoted] write one query to cover all scenarios?

This won't work:

where kindergarten_flag = :p_k_flag
and elementary_flag = :p_e_flag
and junior_high_flag = :p_j_flag
and high_school_flag = :p_h_flag

because in the first scenario, it would only return row 2.

This also won't work:

where kindergarten_flag = :p_k_flag
or elementary_flag = :p_e_flag
or junior_high_flag = :p_j_flag
or high_school_flag = :p_h_flag

because is the first scenario, :p_k_flag and :p_h_flag are 'Y' and the other [Quoted] 2 are 'N', giving

where kindergarten_flag = 'Y'
or elementary_flag = 'N'
or junior_high_flag = 'N'
or high_school_flag = 'Y'

[Quoted] which would return row 6 as well as the ones we want.

I have racked my brain trying to solve this. I am willing to use unions, [Quoted] views, inner selects - anything!!!!

Someone must have solved this before.

I eagerly await any answer.

Eric Janzen Received on Fri Feb 25 2000 - 23:11:24 CET