Multi conditional if statement in SQL query

I have a long list of names formatted like below. A person may have a few different representations of their name in the table (e.g. ‘Smith, Tom’ vs ‘Tom Smith’).

A query will come through using a like search (e.g. "SELECT * FROM table WHERE person_name LIKE "%smith%").

If a person has the boolean field is_display checked as true I want only the row with is_display set to true, if the person ID does not have an is_display checked off, I then want to fall back on the is_preferred field.

So ideally, a search for “smith” would only return rows 1 and 4.

ID  person_id   person_name is_preferred    is_display
1   123456          Tom Smith       0               1
2   223456          Madonna             1               1
3   123456          Smith, Tom      1               0
4   423456          Smith, Frank        1               0
5   423456          Smith, Frank R.     0               0

I’ve looked into SQL if / else and CASE statements, but haven’t found a command that works for this need yet.

Right the closest I’ve gotten is:

SELECT *
FROM artist_aliases
WHERE ( (alias LIKE '%smith%' AND display=1) OR 
      (alias LIKE '%smith%' AND preferred=1) )

Then I massage the results in code after SQL. But I imagine there’s a much more elegant way to do this only in SQL.

Answer

You seem to want a prioritization by person.

In MySQL 8+, this would look like:

select aa.*
from (select aa.*,
             row_number() over (partition by person_id order by display desc, preferred desc) as seqnum
      from artist_aliases aa
      where alias like '%smith%' 
     ) aa
where seqnum = 1;

In earlier versions, you can use a correlated subquery:

select aa.*
from artist_aliases aa
where aa.alias like '%smith%' and
      aa.id = (select aa2.id
               from artist_aliases aa2
               where aa2.alias like '%smith%' and
                     aa2.person_id = aa.person_id 
               order by aa2.display desc, aa2.preferred desc
               limit 1
              );