Sometimes you may wish to filter an sql only if that would give you a record. For example if you have a table that holds several telephone numbers for a customer. The table has a flag "preferred” (True or false) that specifies a users preferred contact number. Now in the SQL we wish to create a list of users complete with telephone numbers. Here is where we hit a snag, due to input errors/business logic bugs. A number of users have been allowed to enter several contact numbers without selecting a preferred one. Therefore we can’t simply filter preferred numbers as this would lead to some customers not having a number were a number does actually exist.

The solution I propose is two fold.

Firstly, We need to know are preferred numbers available for a user. To do this we first make an sql statement that provides this info. In my example lets select all entries grouped by customer id where preferred.
(i.e.

Select CustomerID as CustomerID2 from TelephoneDetails where preferred group by CustomerID

. )

Secondly, To use a xnor logical clause. To create this we need a clause in our sql that states ( (A and B) or ( !A and !B)). Clause A will be simply is the telephone number preferred, clause b will be are preferred numbers available for this customer from above. To apply clause b we must first join the table above to the standard table. To allow non-preferred rows to be returned we need to use an outer join.

Our Sql becomes:

Select * from TelephoneDetails as a left outer join (
Select CustomerID as CustomerID2,  from TelephoneDetails where preferred group by CustomerID
) as b on a.CustomerID = b.CustomerID2
where (( a.Preffered = 1 and b.CustomerID2 is not null) or (a.Preffered = 0 and b.CustomerID2 is null))

This will now return a customers preffered telephone if set, and other telephone if not.

Given Data

CustomerID	Number	Prefered
1	1251	true
1	1255	false
2	1235	false

Standard Filter would give

CustomerID	Number	Prefered
1	1251	true

but our sql returns

CustomerID	Number	Prefered
1	1251	true
2	1235	false