I have a couple of queries in a union all that need to get all records, some from the first where clause, and everything else in the other.
Here is the original where clause:
from license_status ls, pos_driver pd, pos_policy pp where pp.pos_id = @pos_id and pd.pos_id = pp.pos_id and pd.driver_number = @driver and ls.company = pp.company and ls.license_state = pd.license_state
I have added a few additional elements to limit records to the state of washington, for a particular company, and a specific answer to a question.
Here is the new one:
from license_status ls, pos_driver pd, pos_policy pp, pos_answers pa where pp.pos_id = @pos_id and pp.policy_state = 'WA' and pp.company = 5 and pd.pos_id = pp.pos_id and pd.driver_number = @driver and ls.company = pp.company and ls.license_state = pd.license_state and pp.pos_id = pa.pos_id and pa.question_number = 8 and pa.yes_no = 'YES'
So now I need to rewrite the original where clause to get all the records this new where clause excludes. I started by adding this:
and pp.policy_state = 'WA'
But realized there would be some policies in WA that did not meet the other criteria.
So how do I go about this without writing a ton of permutations to get all the possibilities?