MYSQL - Querying a table with optional filters and default value

Question

I have a table that looks like this:

ID	KEY	FILTER_NAME	FILTER_VALUE	VALUE
1	a	country	USA	value1
2	a	inRockBand	true	value2
3	a	value3
4	b	city	MADRID	value4
5	b	isLawer	true	value5
6	b	value6

I would like to create a query that can receive generic filter (optional) parameters and fetches a single row per key accordingly (when optional filters don't match - the query will return the row with null filter name\value as these are the default rows)

for example:

select * from table where filterName = 'country' and filter_value = 'SPAIN'

will return:

ID	KEY	FILTER_NAME	FILTER_VALUE	VALUE
3	a	value3
6	b	value6

while

select * from table where filterName = 'inRockBand' and filter_value = 'true'

will return:

ID	KEY	FILTER_NAME	FILTER_VALUE	VALUE
2	a	inRockBand	true	value2
6	b	value6

if several filters matches several keys we should take any one key

Answer 1

SELECT COALESCE(t1.ID, t2.ID) ID,
 t0.`KEY`,
 COALESCE(t1.FILTER_NAME, t2.FILTER_NAME) FILTER_NAME,
 COALESCE(t1.FILTER_VALUE, t2.FILTER_VALUE) FILTER_VALUE,
 COALESCE(t1.VALUE, t2.VALUE) VALUE
FROM ( SELECT @filterName FILTER_NAME,
 @filter_value FILTER_VALUE ) criteria
CROSS JOIN ( SELECT DISTINCT `KEY`
 FROM test ) t0
LEFT JOIN test t1 ON criteria.FILTER_NAME = t1.FILTER_NAME
 AND criteria.FILTER_VALUE = t1.FILTER_VALUE
 AND t0.`key` = t1.`key`
LEFT JOIN test t2 ON '' = t2.FILTER_NAME
 AND '' = t2.FILTER_VALUE
 AND t0.`key` = t2.`key`

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=feceb7a404ddd6a78324664c51c3c310

Answer 2

The query I used looks like so:

select * from tbl where id in(
 select min(id) 
 from (
 SELECT * 
 FROM tbl 
 where (filterName = 'inRockBand' and filterValue = 'true') 
 or filterName is null
 ) a 
 group by keyId
);