Nik Rubblers Nik Rubblers - 4 years ago 66
MySQL Question

sql where clause - best practice: matching fields are entitled to be selected only if the other fields match, or are set to null

hard to be more explicit in the subject, need to explain the problem.

In this select:

SELECT `id`, `creation_time`, `roster_date`, `roster_type`,`roster_report_time`, `flight_hrs`, `rank`, `email`
FROM `alert_subscriptions`
WHERE 1


I have a fixed combination of the values 'rank', 'roster_date' and 'roster_type' to use in the where clause.
The problem is that I need to find who has inserted only a value in 'rank' and set other fields to null), or who has set 'rank' and 'roster_date' while 'roster_type' is set to null, and so on.

Whoever has a matching field is entitled to be selected only if the other fields match, or are set to null.

One solution of course is to put in the where clause all the possible combinations (in this case 7,eg ranknullnullORnulldatenullORnullnulltypeORrankdatenullORranknulltype.. and so on..) but I would like to find an elegant solution, also easy to update with more fields (this solution would drive me crazy adding more fields to the where clause, the number of combination would rise enormously!)

I'm sure there is a solution with some select or union or view, but when I get close to the solution I get lost and can't make up my mind.

I'm stuck, I need some external point of view.

Thank you everybody

Answer Source

Just check if each criteria is NULL, and treat that as a successful part of the match. You can then combine each criteria test with OR.

WHERE (year IS NULL OR year = 2010)
AND (type IS NULL OR type = 'wagon')
AND (size IS NULL OR size = '2L')
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download