Query puzzle – how can I select values with fallbacks?

I have a table with 6 columns as follows:

CREATE TABLE `test_table` (
  `id` bigint UNSIGNED NOT NULL,
  `A` varchar(255) NOT NULL,
  `B` varchar(2) NOT NULL,
  `C` varchar(50) DEFAULT NULL,
  `D` varchar(10) DEFAULT NULL,
  `E` varchar(10) DEFAULT NULL
) ENGINE=InnoDB;

For searching I am providing target values for B/C/D and selecting columns A and E for a result set.

I would like to obtain a row if:

• B/C/D all match provided values => select it
• B/C match but D is NULL => select it
• B/D match but C is NULL => select it
• B matches and C/D are NULL as a final fallback.

Basically I would like to get a row if conditions are hit in order (eg: if I have a match for all 3, I don’t want a result from the next condition)

So far the closes I’ve come is this:

        SELECT m.*
        FROM test_table m
        WHERE m.id IN (
            SELECT o.id
            FROM (
                SELECT i.id, i.A, i.C, i.D, i.B
                FROM test_table i
                WHERE i.B=?
            ) o
            WHERE m.A = o.A
            ORDER BY o.A, -o.C ASC, -o.D ASC
        ) AND (
            (m.C=? AND m.D=?) OR
            (m.C=? AND m.D IS NULL ) OR
            (m.C IS NULL AND  m.D=? ) OR
            (m.C IS NULL AND m.D IS NULL )
        );

However, I do run into the issue that I get multiple records when C/D are both NULL + C has matching value and D is NULL.

Can someone suggest what I can do?

Thanks!

Answer

You can create a column for each of your conditions, sort the results by the priority of each condition and return the 1st row only:

select a, e
from (
  select *,
    coalesce(b = ?, 0) + coalesce(c = ?, 0) + coalesce(d = ?, 0) cond1,
    coalesce(b = ?, 0) + coalesce(c = ?, 0) + (d is null) cond2,
    coalesce(b = ?, 0) + (c is null) + coalesce(d = ?, 0) cond3,
    coalesce(b = ?, 0) + (c is null) + (d is null) cond4
  from test_table
) t
where 3 in (cond1, cond2, cond3, cond4)
order by (cond1 = 3) desc, (cond2 = 3) desc, (cond3 = 3) desc, (cond4 = 3) desc
limit 1

Or:

select a, e
from (
  select *,
    coalesce(b = ?, 0) and coalesce(c = ?, 0) and coalesce(d = ?, 0) cond1,
    coalesce(b = ?, 0) and coalesce(c = ?, 0) and (d is null) cond2,
    coalesce(b = ?, 0) and (c is null) and coalesce(d = ?, 0) cond3,
    coalesce(b = ?, 0) and (c is null) and (d is null) cond4
  from test_table
) t
where cond1 or cond2 or cond3 or cond4
order by cond1 desc, cond2 desc, cond3 desc, cond4 desc
limit 1