I'm sure someone must have hit this or a problem like this before but I'm not finding anything on a google or DBA exchange search.
I have a table storing key/value pairs. There are also 2 limiting columns in the table, host and environment, both nullable. The way that the limiting tables work is that if there is an entry in the host column then the key/value applies to that host, similarly if there is an entry in the environment column then that entry takes effect only for that environment. If a key/value is sought with a host or environment then the limited values take precedent over the values where host=NULL or environment=NULL. Furthermore, host takes precedence over environment.
So as an example, here are some table entries:
id env host key value 1 NULL NULL fruit apple 2 prod NULL fruit banana 3 prod www fruit mango 4 test NULL fruit peach 5 NULL test fruit orange
Given that example data, what I'm after is a SQL statement (or statements) that will return these data based on these criteria:
env host key data returned NULL NULL fruit 1, fruit, apple prod NULL fruit 2, fruit, banana NULL www fruit 3, fruit, mango junk junk fruit 1, fruit, apple // NULL/NULL values are used as a fallback junk www fruit 1, fruit, apple // no match, fallback prod test fruit 5, fruit, orange // host=test takes precedence
Does that make sense?
I can break the search up into multiple selects, try each one, check to see if I get a hit, etc, but I'm hoping to achieve this as efficiently as possible.
Ideally I would like the type of search that would be easily extensible if more limiters are added later (e.g. group, namespace, etc).