I think that works except:

1. The record in table1 cannot be in table3 period (regardless of 2 and 3 below).
2. If the record in table1 is in table2, then include
it only if table2.fielda <> "X" and table2.fieldb <> "I"
3. If the record in table1 is NOT in table2, include it (as long as it is not in table3 as well).
4. Forgot to add (just to be clear) that the record in table1 must only be included if field2 = "FA" and field3 = 0 period as well.



>SELECT i.* from table1 i
> WHERE (( i.field2 = "FA" and i.field3 = 0)
> OR EXISTS (SELECT * from table2 s WHERE s.key1 = i.key1 and s.fielda <> "X" and s.fieldb <> "I"))
> AND NOT EXISTS(SELECT * from table3 a WHERE a.key1 = i.key1 AND a.field4 = i.field4)
> ORDER BY i.field4
>
>>I need to do the following:
>>
>>

>>SELECT i.* from table1 i
>>   WHERE (IF exists) (SELECT * from table2 s WHERE s.key1 = i.key1 and s.fielda <> "X" and
>>                      s.fieldb <> "I")
>>   AND i.field2 = "FA" and i.field3 = 0
>>   AND NOT EXISTS(SELECT * from table3 a WHERE a.key1 = i.key1 AND a.field4 = i.field4)
>>   ORDER BY i.field4
>>

>>In other words,
>>
>>1. If a record from table1 is in table2, I want to include records from table1 if table2's fielda <> "X" and table2's fieldb <> "I", but if records in table1 whose field2 = "FA" and field3 = 0 are NOT in table2, I want to include those as well.
>>2. Of those records in the result from #1, I only want to include the records that are NOT in table3 (determined by key1 and field4 field matches). (I want to strip out any records from #1 that are already in table3.
>>
>>Right now I am using:
>>

>>SELECT i.* from table1 i
>>   WHERE EXISTS (SELECT * from table2 s WHERE s.key1 = i.key1
>>                 AND s.fielda <> "X" AND s.fieldb <> "I")
>>   AND i.field2 = "FA" and i.field3 = 0
>>   AND NOT EXISTS(SELECT * from table3 a WHERE a.key1 = i.key1
>>                  AND a.field4 = i.field4)
>>   ORDER BY i.field4
>>

>>
>>But that leaves out those records from table1 that are NOT in table2 which I want to include in the result if they are not in table2 and are not in table3 (if table1.field2 = "FA" and table1.field3 = 0)...