SQL Doozy

The Mighty Dubs

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:D

pH

It's going to be difficult (if not impossible) to do this in a single SQL statement.

The normal way of doing this is to dynamically build the SQL

Have a base statement ( "SELECT * FROM table" )

Check the search values entered and add "AND xxx = 'XXXXX'" (or indeed AND xxx LIKE 'xxxx%') to the base.

Then send the sql string to sql and process the results.

bonkey

While I'd generally recommend using pH's approach, you *can* do this using a single statement, and its not difficult at all.

It just requires a little lateral thinking...

SELECT foo
  FROM bar
  WHERE foo.first_search_field = CASE WHEN <first search term> = '' 
                                        THEN foo.first_search_field
                                      ELSE <first search term>
                                 END
    AND foo.second_search_field = CASE WHEN <second search term> = ''
                                         THEN foo.second_search_field
                                       ELSE <second search term>
                                  END
    AND ...                             

So, if a value is supplied, teh record being tested must contain the supplied value in teh appropriate field. Otherwise, the field is compared to itself...which surprisingly enough, should match.

1st caveat : a NULL never equals another NULL. If a field allows NULL values, and you *don't* supply a search-value for that field, this approach won't return the records with NULL values in that field. Of couse, its a trivial problem to overcome....once you know its there.

2nd caveat : I can't comment on the performance of this. My guess is that indexes won't be used by the optimiser, so it becomes a brute-force search. Thats why I generally prefer pH's suggestion - it *does* allow indexes without any question.

jc

pH

There you go, I'm not familiar with SQLServer's CASE statement.

What's the difference between:

WHERE foo.first_search_field = CASE WHEN <first search term> = '' 
                                        THEN foo.first_search_field
                                      ELSE <first search term>
                                 END
AND ...

and

WHERE ( foo.first_search_field = <first search term> = '' 
                                        OR <first search term> = '' )
AND...

Will they both run and produce the same results?

bonkey

pH wrote:

What's the difference between:

WHERE foo.first_search_field = CASE WHEN <first search term> = '' 
                                        THEN foo.first_search_field
                                      ELSE <first search term>
                                 END
AND ...

and

WHERE ( foo.first_search_field = <first search term> = '' 
                                        OR <first search term> = '' )
AND...

Nothing, that I can see, assuming that your version is meant to read:

WHERE ( foo.first_search_field = <first search term>  
                                        OR <first search term> = '' )
AND...

Will they both run and produce the same results?

They should...once you correct the syntax in your version

I have (rightly or wrongly) an aversion to using OR statements nested inside AND statements, and a liking for CASE statements. I can't even explain why, but it tends to fit the way I think about solving problems.

jc

The Mighty Dubs

Hi Guys,

Thanks for the help but Im still a little neanderthal when it comes to this type of thing. What I have gathered from this is the following

SELECT *
FROM dbo.qryUniqueProduct
Where Description = CASE WHEN "txtSearch" = ''
THEN Description
ELSE "txtSearch"
END
AND SupplierID = CASE WHEN "txtSupplier" = ''
THEN SupplierID
ELSE "txtSupplier"
END
AND GroupCode = CASE WHEN "txtgroupID" = ''
THEN GroupCode
ELSE "txtgroupid"
END

But no surprises Im getting errors...am i missing something here?

pH

At this stage you'll probably need to post your asp code, also the exact error message would be handy. Use the [code] tags when posting.

As I said earlier, I'd dynamically build the sql statement, I've rarely used SQL server and aren't familiar with its CASE blocks.

Talisman

Here's some basic but effective code that will build the sql statement dynamically for you.

sSQL = "SELECT * FROM dbo.qryUniqueProduct "
IF (len(txtSearch) > 0) THEN
  BEGIN
    sSQL = sSQL & " WHERE Description LIKE '%" & txtSearch & "%' "
    IF (len(txtSupplier) > 0) THEN
      sSQL = sSQL & " AND SupplierID = '" & txtSupplier & "' "
    END IF
    IF (len(txtGroupID) > 0) THEN
      sSQL = sSQL & " AND GroupCode = '" & txtGroupID & "' "
    END IF
  END
ELSE IF (len(txtSupplier) > 0) THEN
  BEGIN
    sSQL = sSQL & " WHERE SupplierID = '" & txtSupplier & "' "
    IF  (len(txtGroupID) > 0) THEN
      sSQL = sSQL & " AND GroupCode = '" & txtGroupID & "' "
    END IF
  END
ELSE IF (len(txtGroupID) > 0) THEN
      sSQL = sSQL & " WHERE GroupCode = '" & txtGroupID & "' "
END IF

stevenmu

I may be missing something obvious (it is friday afternoon after all ) but I think you could do something as simple as

myQuery = "SELECT * FROM table WHERE ItemDescription LIKE '%" + txtSearch + "%' AND SupplierID LIKE '%" + txtSupplierID + "%' AND GroupID LIKE '%" + txtGroupID + "%' "

This way if any of your values txtSearch,txtSupplierID or txtGroupID are empty (but not null) the clause in your query will be LIKE '%%' which should match everything (except null fields I think), meaning only the terms with an actuall value will make any difference. Shouldn't perform *too* badly either, but depending on your table size and indexing etc, you may be better off just having a seperate query string for each scenario and using your ASP code to pick the appropriate one.

pH

stevenmu wrote:

I may be missing something obvious (it is friday afternoon after all ) but I think you could do something as simple as

Yes, the LIKE '%xXXXX%' won't work for an exact match on supplierID.

If he has a supplierID of 'AA' and a supplierID of 'A' then searching for 'A' -
LIKE '%A%' will match both 'A' and 'AA' - he was specifically looking for exact matches on the IDs, only description was a partial match.

outofcoolnames

heres a c# approach
//passing the text,cbo1 and cbo2 values
//avoid select * btw

public void resolveQuery(string txtSearch,string cboSupplier,string cboGroupId)
{
//assign and all that stuff
...
SELECT
up.ItemDescription,
up.SupplierID
up.GroupID
FROM
dbo.qryUniqueProduct up
WHERE
up.SupplierId = NVL(Convert.toInt32(this.cboSupplier.getSelectedItem.trim()),NULL)
AND
up.GroupId = NVL(Convert.toInt32(this.cboGroup.getSelectedItem.trim()),NULL)
AND
up.ItemDescription LIKE (this.txtSearch.trim())
;

}

The Mighty Dubs

:eek: How doin,

Am getting this error through Analyzer when i run your code below

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '
IF (len(txtSupplier) > 0) THEN
sSQL = sSQL & '.
Any ideas

Talisman wrote:

Here's some basic but effective code that will build the sql statement dynamically for you.

sSQL = "SELECT * FROM dbo.qryUniqueProduct "
IF (len(txtSearch) > 0) THEN
  BEGIN
    sSQL = sSQL & " WHERE Description LIKE '%" & txtSearch & "%' "
    IF (len(txtSupplier) > 0) THEN
      sSQL = sSQL & " AND SupplierID = '" & txtSupplier & "' "
    END IF
    IF (len(txtGroupID) > 0) THEN
      sSQL = sSQL & " AND GroupCode = '" & txtGroupID & "' "
    END IF
  END
ELSE IF (len(txtSupplier) > 0) THEN
  BEGIN
    sSQL = sSQL & " WHERE SupplierID = '" & txtSupplier & "' "
    IF  (len(txtGroupID) > 0) THEN
      sSQL = sSQL & " AND GroupCode = '" & txtGroupID & "' "
    END IF
  END
ELSE IF (len(txtGroupID) > 0) THEN
      sSQL = sSQL & " WHERE GroupCode = '" & txtGroupID & "' "
END IF