SQL queries to determine all values that would satisfy an arbitrary query
- by jasterm007
I'm trying to figure out how to efficiently run a set of queries that will provide a new table of all values that would return results for an arbitrary query.
Say my table has a schema like:
id
name
age
city
What is an efficient way to list all values that would return results for an arbitrary query, say "NOT city=X AND age BETWEEN Y and Z"?
My naive approach for this would be to use a script and recurse through all possible combinations of {city, age, age} and see which SELECTs return more than 0 results, but that seems incredibly inefficient. I've also tried building large joins on {city, age, age} as well and basically using that table as an argument list to the query, but that quickly becomes an impossibility for queries on many columns.
For simple conjunctive equality queries, i.e. "name=X and age=Y", this is much simpler, as I can do something like
SELECT name, age, count(*) AS count
FROM main GROUP BY name, age HAVING count > 0
But I'm having difficulty coming up with a general approach for anything more complicated than that.
Any pointers in the right direction would be most helpful, thanks.
