The Kat's Work - Blog
Main | Blog | Registration | Login
Sunday
2017-09-24
3:19 AM
Welcome Guest | RSS
Main » 2011 » October » 12 » SQL Conditional Filter
4:50 PM
SQL Conditional Filter

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

CustomerIDNumberPrefered
11251true
11255false
21235false


Standard Filter would give

CustomerIDNumberPrefered
11251true


but our sql returns

CustomerIDNumberPrefered
11251true
21235false
Views: 2380 | Added by: The_Kat | Tags: SQL | Rating: 0.0/0
Total comments: 0
Name *:
Email *:
Code *:
Login form
Adverts
Search
Calendar
«  October 2011  »
SuMoTuWeThFrSa
      1
2345678
9101112131415
16171819202122
23242526272829
3031
Entries archive
Site friends
  • Create your own site
  • Spree4.com
  • My Blog
  • Statistics

    Total online: 1
    Guests: 1
    Users: 0
    spree4
    Copyright MyCorp © 2017
    Website builderuCoz