Users selection driven search query...

mat41 · November 15th, 2004, 02:27 AM

When a user makes choices on a search page a query is created based on their selections, in the example below there are three criteria that arrive in form variables.

What is the best way to create the query? As you can see I have a 'WHERE 1=0' in there to create a false-true scenario. This seems a bit of a hack?

A minimum of one of the three criteria will be selected, since we donât know which one how do we know which conditional statement to place the AND (this would eliminate the need for the 'WHERE 1=0' part of the query)

NOTE: there are no errors in the query it runs fine
OBJECTIVE: I would like to get rid of the 'WHERE 1=0'

SQL = "Select DISTINCT products.ID, products.description,Products.forder FROM products LEFT OUTER JOIN ProductProfiles ON products.id = productprofiles.productId " & _
    "LEFT OUTER JOIN users ON productProfiles.prodManager = users.ID " & _
    "WHERE 1=0 "
    if request.form("pn") = "on" then
       sql = sql & " OR (Products.description LIKE '%" & SearchString & "%') "
    end if
    if request.form("sd") = "on" then
       sql = sql & " OR (ProductProfiles.Description LIKE '%" & SearchString & "%') "
    end if
    if request.form("nd") = "on" then
       sql = sql & " OR (Products.natDescription LIKE '%" & SearchString & "%') "
    end if
    sql = sql & " AND productProfiles.RegionID = " & IRegion & " "
    sql = sql & " ORDER BY Products.forder"

Thank you in advance

Wind is your friend
Matt

happygv · November 15th, 2004, 03:47 AM

Hi Matt,

As you say atleast one of those 3 criteria would be imposed always. It could be removed(1=0) and used as below.

Code:

SQL = "Select DISTINCT products.ID, products.description,Products.forder FROM products LEFT OUTER JOIN ProductProfiles ON products.id = productprofiles.productId " & _
    "LEFT OUTER JOIN users ON productProfiles.prodManager = users.ID "
    sql = sql & " AND productProfiles.RegionID = " & IRegion & " AND "

    Dim strWhere
    strWhere = ""
    if request.form("pn") = "on" then
       strWhere = strWhere & " (Products.description LIKE '%" & SearchString & "%') "
    end if
    if request.form("sd") = "on" then
       if len(strWhere) > 0 then strWhere = strWhere & " OR "
       strWhere = strWhere & " (ProductProfiles.Description LIKE '%" & SearchString & "%') "
    end if
    if request.form("nd") = "on" then
       if len(strWhere) > 0 then strWhere = strWhere & " OR "
       strWhere = strWhere & " (Products.natDescription LIKE '%" & SearchString & "%') "
    end if
    sql = sql & " ORDER BY Products.forder"

I haven't tested this. This might need slight alterations if needed.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

mat41 · November 15th, 2004, 06:24 PM

happygv

ahhh:
;;;if len(strWhere) > 0 then strWhere = strWhere & " OR "
Was the seed for the thought train I was stumbling over

Whats your thoughts on my: false-true 'WHERE 1=0' method, is this a hack?

Thanking you

Wind is your friend
Matt

happygv · November 16th, 2004, 03:14 AM

Hi Matt,

I wont say that is a hack. That is another way of achieving it. IMO both the way of approach are fine. WHERE 1=0 is just a work around to achieve it in another way. There shouldn't be any issues in going that way at all.

Cheers!

_________________________
- Vijay G
Strive for Perfection