MySql multiple where conditions - only match first correct condition

Question

With data similar to the table below:

|  id  |    e_date    |  e_time |  place | person| ref  |ref_type| 
|  10  |  2015-08-03  |  10:30  |  work  |  tony | 1234 |   A    |
|  25  |  2015-08-03  |  10:30  |  work  |  NULL | NULL |   A    |
|  37  |  2015-08-03  |  NULL   |  work  |  tony | NULL |   A    |
|  99  |  2015-08-03  |  10:30  |  work  |  fred | 1234 |   B    |

What's the best method to get only the 1st match of a series of conditions (of desc importance) in a MySQL WHERE clause ?

match the ref field
if no matches in ref field, then match on e_date+e_time+place fields
if no matches on ref or e_date+e_time+place then match on e_date+place+person

The aim here is to get the best single row match - based on a descending series of criteria - and only use the criteria if the preceding criteria isn't fulfilled.

My first attempt at query looked like:

SELECT id FROM my_table 
WHERE ref_type = 'A' AND (
  ref = '1234'
  OR
  (e_date = '2015-08-03' AND e_time = '10:30' AND place = 'work')
  OR
  (e_date = '2015-08-03' AND place = 'work' AND person = 'tony')
)

but since OR is inclusive (not sequential), this returns rows 10, 25 and 37 - see this sqlfiddle

I could ORDER BY ref DESC and LIMIT 1 (if I modify to SELECT id, ref FROM...) but that doesn't help me if I have no ref value and have to differentiate by either of the 2nd or 3rd conditions

My next attempt uses nested IF conditions in the WHERE clause like :

SELECT id FROM my_table 
WHERE ref_type = 'A' AND (
  IF(ref = 1234, 
    ref = 1234,
    IF(e_date = '2015-08-03' AND e_time = '10:30' AND place = 'work', 
       e_date = '2015-08-03' AND e_time = '10:30' AND place = 'work',
       e_date = '2015-08-03' AND place = 'work' AND person = 'tony'
    )
  )
)

However, this returns also rows 10, 25 and 37 - see this sqlfiddle

Also tried using IFNULL :

SELECT id FROM my_table 
WHERE ref_type = 'A' AND 
  IFNULL(ref = '1234', 
    IFNULL(e_date = '2015-08-03' AND e_time = '10:30' AND place = 'work',
      e_date = '2015-08-03' AND place = 'work' AND person = 'tony')
  )

Which returns rows 10 and 25 - see this sqlfiddle

What's the best way to write this query ?

I'm using php - and I could run 3 separate sequential queries and use php conditionals on each result - but I'd like to use a single db query given the millions of times this code will be run per hour.

What do you mean by "`ORDER BY ref DESC LIMIT 1` doesn't help me if I have no `ref` value "? You are fetching an `id` column not `ref`.So using `LIMIT 1` on the first query(with simple `OR`) clause should work right? — Kamehameha, Jul 07 '15 at 09:26
you tagged `mysql` and `sql-server`, which one are you using ? — Pholochtairze, Jul 07 '15 at 09:26
@Pholochtairze `mysql` - sorry will remove the `sql-server` tag — goredwards, Jul 07 '15 at 09:30
@Kamehameha - yes correct - i'd need to `SELECT id, ref FROM...` in that case - i'll update for clarity - but as noted in the question, a `LIMIT` doesn't help me distinguish from conditions 2 and 3 if the 1st `ref` condition is not fulfilled — goredwards, Jul 07 '15 at 09:34

score 3 · Answer 1 · answered Jul 07 '15 at 09:27

3

The only way (that I know of) is to run the query multiple times with a different where. You can do that as a single query with union:

SELECT * FROM (
    SELECT <stuff> FROM <table> WHERE <most important condition> LIMIT 1
    UNION
    SELECT <stuff> FROM <table> WHERE <less important condition> LIMIT 1
    UNION
    SELECT <stuff> FROM <table> WHERE <even less important condition> LIMIT 1
    UNION
    ...
) as t
LIMIT 1

Or you can just run them one by one and stop if there is a result.

answered Jul 07 '15 at 09:27

Vatev

7,493
1
32
39

Is this also possible?`SELECT * FROM ( SELECT FROM WHERE LIMIT 1 UNION SELECT FROM
WHERE LIMIT 1 UNION SELECT FROM
WHERE LIMIT 1 UNION ... ) as t LIMIT 1 UNION SELECT FROM
` i mean a union inside a union?
– Name Jan 25 '16 at 14:26

score 1 · Answer 2 · answered Jul 07 '15 at 09:35

Try this :

SELECT
    id
FROM my_table
WHERE ref_type = 'A'
ORDER BY
(CASE WHEN ref = '1234' 
    THEN 1
    ELSE 
        (CASE WHEN e_date = '2015-08-03' AND e_time = '10:30' AND place = 'work' 
            THEN 2
            ELSE
                (CASE WHEN (e_date = '2015-08-03' AND place = 'work' AND person = 'tony')
                    THEN 3
                    ELSE 4
                END)
        END)
END)
LIMIT 1

Or, if it had a long running time create a view with the following query :

SELECT
    id,
    (CASE WHEN ref = '1234' 
        THEN 1
        ELSE 
            (CASE WHEN e_date = '2015-08-03' AND e_time = '10:30' AND place = 'work' 
                THEN 2
                ELSE
                    (CASE WHEN (e_date = '2015-08-03' AND place = 'work' AND person = 'tony')
                        THEN 3
                        ELSE 4
                    END)
            END)
    END) AS "case_field"
FROM my_table
WHERE ref_type = 'A'

And then just run :

SELECT id FROM your_view ORDER BY case_field LIMIT 1;

The first query seems to be working fine. Thank you. – Chetan Oswal Feb 03 '20 at 07:38 — Chetan Oswal, Feb 03 '20 at 07:38

score 0 · Answer 3 · answered Jul 07 '15 at 09:37

0

If I understood your logic, you want to return the row with ref if it's not null. If it's null, then the one with e_time not null. And finally, the one with person not null. So, maybe you could use this ordering to your first attempt query?

SELECT id FROM my_table 
WHERE ref_type = 'A' AND (
  ref = '1234'
  OR
  (e_date = '2015-08-03' AND e_time = '10:30' AND place = 'work')
  OR
  (e_date = '2015-08-03' AND place = 'work' AND person = 'tony')
)
ORDER BY ref DESC,
  e_time DESC,
  person DESC 
LIMIT 1 ;

answered Jul 07 '15 at 09:37

lpg

4,897
1
16
16

see http://sqlfiddle.com/#!9/bbce0/6 - gives results in order `10` `37` `25` - for this data set the required `ORDER BY` clause would need `ASC` on `e_time` to get the conditions sequence correct - so unfortunately it's a 'fragile' solution because while I can force it to work on the current data in this case, required sort it that could be different for another data set – goredwards Jul 07 '15 at 16:48
* the required order `ASC` or `DESC` could be different for another data set, even when using the same `WHERE` conditions – goredwards Jul 07 '15 at 18:13

score 0 · Answer 4 · answered Jul 07 '15 at 10:07

You should try case when in where,

SELECT id FROM my_table 
WHERE 
1 = case when ref_type = 'A' then 1 
    else
        case when ref = '1234' then 1 
        else
            case when (e_date = '2015-08-03' AND e_time = '10:30' AND place = 'work') or (e_date = '2015-08-03' AND place = 'work' AND person = 'tony') then 1 else 0 end 
        end
    end

It will check the conditions in ascending manner if any one the satisfy the criteria then it will not checking more.

Not sure about your exact conditions so that just putted here for the reference you should use that way to fulfill your need.

MySql multiple where conditions - only match first correct condition

4 Answers4