Select * from table where a = a and b = *

[tonyfitz]

Hi Everyone

I am developing a jobs site where employers post their jobs and job seekers can run a search from a search form.

The search form has 4 criteria each with dropdown options to pick from. the first option in each criteria is "ALL" which I want to return all values from that database field.

select * from jobs where title="Accountant" and type ="permanent" and location="all"

This should return all the permanent job vacancies for accountants for all locations

This does not work for me as it assumes "ALL" is a value rather than a request to return all values in that field.

I require limited results for some fields and unlimited results from another field.

How do I do this????

 

[GHolden]

Swap the = for LIKE and if you want 'ALL' put *

eg.

select * from jobs where title LIKE 'Accountant' and type LIKE 'permanent' and location LIKE '*'



There are two ways to write error-free programs; only the third one works.

 

[r937]

it would be better not to include the condition rather than have it test for LIKE '*' (although a smart optimizer might realize that this will be true for any value, and simply discard it)

since this is a site, you probably have ASP as your scripting language, so simply test for the "all" option and don't generate the condition

see
The "any" option in dynamic search SQL, Part 1

http://tinyurl.com/95uf

rudy