Where statement issue

[abathurst]

I have a form where a user can search for properties from a database. Everything is working except for the suburb search. I would like the user to be able to search for more then one suburb.

At the moment I have the information from the textbox being separated into individual ‘words’ and put into a variable @suburb_Statement which looks like:

For 1 word: @suburb_Statement = and ( suburb like 'word1' )
For 2 words: @suburb_Statement = and ((suburb like 'word1' ) or (suburb like 'word2' ))

What I need to do is get that variable into the WHERE statement in the code below but I’m not sure how to do that.

Any help would be great.

select 
'ID: ' + uniqueID as uniqueID,
headline as headline,
subNumber as subNumber,
streetNumber as streetNumber,
street as street,
suburb as suburb,
'$' + convert (varchar (50),price,105) as price,
bedrooms as bedrooms,
bathrooms as bathrooms,
garages as garages,
description as description,
image1 as URL1, 
category as category,
Ref_No as RefNo,
Rowguid

from Realestate

Where @RegID = 'Admin' and price >= @price_min and price <= @price_max
and bedrooms >= @bed and bathrooms >= @bath and garages >= @car

[adam_k]

How are you executing this query (passing vars and all)?
I'd suggest using IN instead of OR. You'll have to provide a list of values like 'value1','value2','value3'.
Depending on the way you pass parameters and execute your query you could pass this as 1 string:

and suburb in (@subs_list)

1 thing I don't really get is how you are going to handle a suburb with 2 words as it's name (With a quick zoom over NY I got West Caldwll, Essex Fells, New Providence, New Milford, Fair Lawn. How are you going to include them if you are splitting words? You'll end up with (suburb = 'Fair' or suburb = 'Lawn')