MYSQL query that returns a default row if no other row is matched

user_4715656 0
Viewed 78

I have an example table "cities" like this:

id   |   city
----------------
1      London
2      Liverpool
3      Birmingham
4      (Other)

Can I write a query that returns (Other) if I provide a non-existing city... such as:

SELECT * FROM cities WHERE city = "London"      --> {1, London}
SELECT * FROM cities WHERE city = "Manchester"  --> {4, (Other) }

I understand that the second SELECT-statement will return nothing. But how can I modify it to return the "(Other)" row? Conceptual code:

SELECT * FROM cities WHERE city = "Manchester" OTHERWISE WHERE city = "(Other)"
mysql sql
4 Answers

One method is:

select c.*
from cities c
where c.city in (?, '(Other)')
order by c.city = '(Other)'
limit 1;

This retrieves the two rows possible rows that might match (? is a placeholder for the name you want). If there are two rows, then then one that is not "other" is chosen.

user_11440350

Consider:

select * 
from cities 
where 
    city = 'Manchester'
    or (city = '(Other)' and not exists (select 1 from cities where city = 'Manchester'))
user_106767160 
select DISTINCT 'Other' AS city
FROM cities WHERE NOT EXISTS (select * from cities where city = 'Brisbane')
UNION ALL
SELECT city from cities where city = 'Brisbane'
user_2767300

Using self join with a slight twist

select a.id, a.city
from (select *, city = '(Other)' as other from cities)  a
left join cities b on b.city = 'London'
where a.city = b.city or (b.city is null and a.other = 1)

Demo

user_99471590
Related