How to fallback to secondary search condition in sqlite WHERE clause?

Question

Suppose that I have a table like this

id title  name
---------------
1  sales   Alex
2  sales   Bob
3  manager Sam
4  manager Willy

When I query this database I will always have available to me a valid value for a title column, but may not have a valid value for a name column.

How do I construct a SELECT * FROM table_name WHERE statement that first searches for a row that matches title='value' AND name='value' and if that does not return a result then fallbacks to just returning the first occurrence of a row that matches title='value'? I am trying to accomplish this in a single query.

For example if title='sales' AND name='Bob' is queried for then the second row is returned. However if the value that I have for name is null or empty (title is still title='sales') then just the first row is returned.

Answer 1

SELECT * 
FROM   mytable t 
WHERE  t.title = 'sales' 
       AND ( t.name = 'something' 
              OR NOT EXISTS (SELECT * 
                             FROM   mytable t2 
                             WHERE  t2.title = t.title 
                                    AND t2.name = 'something') ) 
LIMIT  1;