filter based on selection, which may be "all" (was "help with SQL")

i have a table with following columns:
name, city, state, country.

i want to be able to filter data based on the user's selections. that is name of the person, city, state, country. (city, state, country are menus/lists containing "ALL" option as the first option meaning they some may be selected as "ALL"). also 'name' is a text field and it may be empty. How can I perform this filter. I desperatly need help!.

the idea is to generate a WHERE condition whenever there is a filter condition, and not generate a WHERE condition when there isn't

in psuedocode --

Code:

---

select foo, bar
  from table
 where 1=1
if city_param <> "all"
  and city = 'city_param'
endif
if state_param <> "all"
  and city = 'state_param'
endif
...

---

the reason for starting the WHERE clause with 1=1 is so that if there are no conditions at all, then all rows will be returned (since 1=1 is true for all rows)

personallly, i prefer 937=937, but it's all just a matter of style. :)

i can't grasp this. i guess i'm stupid. can someone please write the actual SQL statement that i would use??

the actual SQL is just like it is in post #2

except you'd use your actual column names instead of foo and bar (these are mere syntactic placeholders)

the stuff in red, of course, isn't part of the SQL, it merely governs whether that particular AND condition is actually incorporated into the SQL or not

if the dropdown parameter is "all", don't generate the condition

similarly for the text field, if it's missing (zero length) then don't generate the condition

Occasionally use a COALESCE hack, if don't want to dynamically generate SQL.

Code:

---

SELECT foo, bar FROM table
WHERE COALESCE(:cityParam, city) = city
AND COALESCE(:stateParam, state) = state
AND COALESCE(:countryParam, country) = country

---

so if any of the parameters are NULL they wont filter.

Yea!