How to Add IF ELSE in MySQL Query

MySQLselect

My query is like this 

select * from mytable where rank <= 10000 and priority = 1

I want to rewrite it like this.

  • Select records which are under a specific value and priority is one.
  • If no records found with priority = 1, then run the query without priority. 
select * from mytable where rank <= 10000

I know it can be done with php but can it can done in one query instead using php ?

thanks

Best Answer

There may be a more elegant or performant solution, but here's a simple option:

select * from mytable
 where rank <= 10000
   and (   priority = 1
        OR NOT EXISTS (SELECT 1 FROM mytable
                        WHERE rank <= 10000
                          AND priority = 1
                      )
       )
;

This works as follows: the NOT EXISTS statement will either evaluate to TRUE for every record where the rank meets our criteria, or to FALSE for every such record. So, if none of the records that meet our other criteria have priority = 1, this is TRUE for all those records, and all records that meet our other criteria will be returned.

If there are records that meet our other criteria and have priority = 1, then this statement will be FALSE for all records that meet our other criteria. In that case, only records where the other branch of the OR is TRUE - where priority = 1 - will be returned.

NOTE: for this to work properly, the NOT EXISTS subquery must check all the query's criteria. If you actually want records where:

  • fieldA is between Jan 1 2017 and March 31 2017; and
  • fieldB is less than 240000

Then the query would have to be:

select * from mytable
 where fieldA >= '2017-01-01' AND fieldA < '2017-04-01'
   and fieldB < 240000
   and (   priority = 1
        OR NOT EXISTS (SELECT 1 FROM mytable
                        WHERE fieldA >= '2017-01-01' AND fieldA < '2017-04-01'
                          AND fieldB < 240000
                          AND priority = 1
                      )
       )
;
Related Question