Hi All,

having problems with an SQL statement I trying to get working. Heres the scenario

I have 3 input fields in a search form
1 text field called txtSearch
2 dropdowns called txtSupplier & txtgroupid

All the relevant info is in the same table called tblProducts

Im trying to get a statement thats functions as follows

If I enter in descriptive text in txtSearch alone, it brings back everything thats 'LIKE' that text.
If I select either of the dropdowns, it brings back everything thats associated to that selection and not the other 2 unselected options.
If I select both dropdowns, it brings back everything that falls into both catagories and not the unedited txtSearch.
If I enter in descriptive text in txtSearch & select from either dropdown they narrow down the results to everything that matches the 3 selections.


My Table would be made up of colums something like this

ItemDescription(txtSearch) SupplierID(txtSupplier) GroupID(txtGroupID)
pen ACME 1
cap AAAA 2
ball BBBB 3
ruler ACME 1
eraser AAAA 1

and so on.....

Im currently using ASP pages and SQL Sequal Server


SELECT *
FROM dbo.qryUniqueProduct
WHERE (Description LIKE '%rsSearch%' OR SupplierID = '%rsSearch1%') OR (Description LIKE '%rsSearch%' OR GroupCode = '%rsSearch2%') OR (SupplierID = '%rsSearch1%' OR GroupCode = '%rsSearch2%')

Name DefaultValue Run-Time Value
rsSearch % Request.QueryString("txtsearch")
rsSearch1 % Request.QueryString("txtSupplier")
rsSearch2 % Request.QueryString("txtgroupid")


Any help would be appreciated

Cheers

What ISNT the query doing? Have you tried using a stored procedure??

Personally I would do something like

Create procedure usp_search @Value1 varchar(255) @value2 varchar(255) @value3 varchar(255)

//Block to handle first condition
Select * from table where column LIKE @value1 +'%'

//blocks to handle second condition
Select * from table where column = value2
Or
Select * from table where column = value3

//block to handle third condition
Select * from table where column = value2 and column = value3

//block to handle last condition
Select * from table where column like @value1 + '%' and column = value2 and column = value3


--Stole this from a moderator

I will only tell you how to do it, not do it for you.
Unless, of course, you want to hire me to do work for you.