Gregory and everyone,
Thanks a lot for your help. I asked clarifications from the client, because it is still not clear to me.
>>Hi everybody,
>>
>>I have the following criteria on my form:
>>
>>Quotes Generated for Manufacturer [ ] Model [ ]
>> Exclude Customers who have purchased
>> Exclude Customers who haven't purchased
>> Model year from [ ] through [ ]
>>
>>I'm trying to translate this into where condition.
>>
>>Exclude customers who have purchased is clear enough:
>>
>>where not exists (select 1 from myTable where Make = [] and Model = [] and Model year between [] and [])
>>
>>But how should I translate the second possibility - Exclude Customers who haven't purchased?
>>
>>Thanks in advance.
>
>When I have a situation like that, I take a sheet of paper + a pencil and move away from the keyboard
>
>When I was a student, I was taught something like algebra of sets
>
>let C be the set of customers
>let A be the set of customers who purchased
>let B be the set of customers who did not purchase
>
>It is clear that C = A + B ( where + stands for the union)
>
>A would translate to something like [where exists(..a purchase..)]
>B would translate to [where not exists(.. a purchase.)
>
>excluding A = not A becomes [ not exists()]
>excluding B = not B becomes [ not not exists()] or simply [exists()]
>
>Excluding both A and B becomes the empty set or [ where false ]
>
>
>Might be simpler to understand if you were asking
>- include Customers who have purchased
>- include Customers who haven't purchased
>
>then including both of them becomes [ where ( A or B )]
>Since we know that C = A + B, it becomes [ where true ] (or no condition at all )
>
>
>
>In addition: A equals not B and B equals not A - since A + B = C
>
>
>Update
>There's also something like boolean algebra - the exclamation mark stands for not
>see :
http://en.wikipedia.org/wiki/De_Morgan%27s_laws>
>!( a and b) = !a or !b
>!( a or b ) = !a and !b
>
>excluding both a and b
>!a and !b = !(a or b)
>
>since
>(1) a = !b
>(2) b = !a
>(3) c = a + b
>
>excluding both a and b
>!a and !b = ( !a and !!a) = !(a or !a) = !(a or b) = !(c) = nothing
>
>
If it's not broken, fix it until it is.
My Blog