Multi conditional if statement in SQL query

Question

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.

Based on your explanation why would a search for "smith" return just rows 1 and 4 and not 1, 3 and 4? — Giddy Naya
– Giddy Naya, Commented Aug 11, 2019 at 23:57
Okay now I understand... Reversed names are still counted as one. — Giddy Naya
– Giddy Naya, Commented Aug 12, 2019 at 0:00

Gordon Linoff · Accepted Answer · 2019-08-11 23:48:04Z

2

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
              );

answered Aug 11, 2019 at 23:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Multi conditional if statement in SQL query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related