0

I have a need to select a filename based on certain options. I have encoded a series of options in a database table, as such:

__A___B___C__ ..  __FEET_____FILENAME_____
| * | * | * |     |    0  |  ABC.pdf      |
------------------------------------------|
| * |   | * |     |    1  | A_C_1foot.pdf |
------------------------------------------

Where several fields, such as A, B, C are booleans, and some other fields, like FEET is a positive integer. When FEET is 0, it means that it is a wildcard, and any positive integer should match that row. I have several other variables like FEET. Those are SHELLS, MODEL.

Only one row max should be returned for any set of options, and only one row min should be returned, since the table guarantees there is one match, but does not guarantee that it is exact. In other words always return exact match (or best match if exact is not avaialble), and one or the other ro both are guaranteed to be there.

Problem

I don't know ahead of time if the table has exact matching row, or if it has wildcards. It is possible that there may even be both, where exact result should be favored, if available. My SQL is like this:

SELECT filename 
FROM table
WHERE A = $a 
  and B = $b 
  and C = $c
  and FEET = $feet
  and MODEL = $model
  and SHELLS = $shells;

When there is no exact match for all FEET, MODEL, and SHELLS, it fails and returns 0 rows for certain FEET, SHELLS, MODEL values. I need to return best matched row.

Dennis
  • 7,907
  • 11
  • 65
  • 115

1 Answers1

0

Alter SQL to say 

SELECT filename WHERE ... 
    AND (FEET = $feet or FEET = 0) 
    AND (SHELLS = $shells or SHELLS = 0) 
    AND (MODEL = $model or MODEL = 0) 
ORDER BY FEET DESC, SHELLS, MODEL
LIMIT 1

to exploit the fact that these fields might contain positive numbers, but when they don't, they are selected as wildcards. LIMIT 1 for the case when you have entries for both 0 and X numbers, to where specific positive number number is favored first, if it is in the database, but if not, a wildcard is selected.

Dennis
  • 7,907
  • 11
  • 65
  • 115
  • Isn't this query reducing the *entire result set* to just one record? I understand the original intention is to get 1 record per group. This question relates: http://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group –  Oct 24 '14 at 14:24
  • well this is related to a product line, where various options supposed to lead you to only one drawing to best represent the product. So, yes, one record is what is expected from the given options, no more, no less. If by chance, data did not forsee a certain configuration, it is acceptable to return 0 rows (and alert the admin of this). But when returning more than 1 row, only one of those is best, and others should be discarded. – Dennis Oct 24 '14 at 14:29