1

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.

6
  • 1
    I don't see how item = pen, color = blue and type = gel gets you 006, is there a typo or what is the logic that you are using? Also where is the color = orange to get a result of 004? Do you want the row that returns the most items matched?
    – Taryn
    May 13, 2015 at 19:03
  • Yes, the maximum matches. The idea is that some items are grouped: all 'gel pens' that aren't green are 006, which includes 'orange'. May 13, 2015 at 19:12
  • What if there is an extra row with item = pen, color = blue, type = NULL, part_number=008. What should the query item = pen, color = blue and type = gel return then? 006 or 008? Both would have two matches. May 13, 2015 at 19:12
  • @ypercube That would be fine (as it would be an input error) EDIT: I mean that it is impossible since that cannot happen without the table being wrong. May 13, 2015 at 19:14
  • ORDER BY (pen='blue')+(type='gel')+(color='blue') DESC, (pen='blue')+(type='gel') DESC, (pen='blue') DESC LIMIT 1; May 13, 2015 at 19:21

1 Answer 1

3

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 ;
2
  • Maybe better (shorter)?: ORDER BY 4*(item='pen')+2*(type='gel')+(color='blue') DESC
    – Rick James
    May 13, 2015 at 19:52
  • @RickJames for the 2nd query? Yes, nice! May 13, 2015 at 19:53

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.