1

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

2 Answers 2

0
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

0

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
);
1
  • Is this your answer ? Commented Feb 2, 2023 at 12:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.