Selecting the correct row given multiple input value combinations

Question

I have a table that stores part numbers for various items like so:

+------+-------------+-------------+-------------+
| Item |    Color    |    Type     | Part Number |
+------+-------------+-------------+-------------+
| Pen  |             |             |         001 |
| Pen  |       red   |    felt     |         002 |
| Pen  |      blue   |    felt     |         003 |
| Pen  |             |    felt     |         004 |
| Pen  |     green   |    felt     |         005 |
| Pen  |             |     gel     |         006 |
| Pen  |     green   |     gel     |         007 |
+------+-------------+-------------+-------------+

I need to get the part numbers for items that were ordered in the past. Is there a way to match the "correct" row with a single query knowing the Item, Color and Type?

Given {Item = Pen, Color = orange, Type = felt} the result should be 004

Given {Item = Pen, Color = blue, Type = felt} the result should be 003

Given {Item = Pen, Color = blue, Type = gel} the result should be 006

I'd like to avoid populating every possible combination into the table if possible.

Answer 1

Short and dirty answer. Not efficient, it will have to scan the whole table. Will give only 1 row as result, if there are more than one rows with same (maximal) number of matches ("wrong" data in your terminology), the choice is arbitrary:

SELECT *
FROM tableX
ORDER BY (item='pen')+(type='gel')+(color='blue') DESC
LIMIT 1 ;

Slightly better than the above but not more efficient. If there are more than one rows with same (maximal) number of matches, the choice is not arbitrary but based on the assumption that a row with "item and type" is preferred over a row with "item and colour" which is preferred over a row with "type and colour":

SELECT *
FROM tableX
ORDER BY (item='pen')+(type='gel')+(color='blue') DESC, 
         (item='pen')+(type='gel') DESC, 
         (item='pen') DESC 
LIMIT 1 ;