Mysql – How to check three conditions on a select with one query

MySQLselect

I have a table containing email text for welcoming new users. There is a default email, one that may be client_id specific and one that may be user_id specific. I can do three queries to find user, then if not found. client, then if not found, default. But I'd prefer to have one query that selects the one that follows that priority list of user, client and default.

The other option is to do one query that may return up to three rows and then just select the appropriate row. But that is messy…

SELECT * FROM emails 
WHERE primary_key=? 
  AND ((user_id=?) 
       OR (user_id IS NULL AND client_id=?) 
       OR (user_id IS NULL AND client_id IS NULL))

This may return up to three rows. Is there a way to just return one row?

Best Answer

You can use Conditional ORDER BY and then use LIMIT 1 to restrict result-set to one row. This approach depends on a MySQL feature where it implicitly typecasts the result of a conditional expression, i.e., boolean to int (0/1)

SELECT * FROM emails 
WHERE primary_key=? 
ORDER BY 
  user_id = ? DESC,  -- if user_id matches, it will be 1 and come first 
  client_id = ? DESC 
  -- does not matter if user_id matches, 
  -- but if it does not, then client_id matches will come first
LIMIT 1
Related Question