Help needed creating SQL statement for search interface in coldfusion

1. It looks like what you need is an AND query and not an OR query.  As I understand it, you're looking for all records that match ONLY the values selected, but in BOTH (or ALL THREE) fields.  So the query should look more like:

SELECT *
FROM business
WHERE
Region LIKE '#FORM.regionMenu#'
AND Business_Type LIKE '#FORM.business_typeMenu#'
AND Asking LIKE '#FORM.askingMenu#'

2. Another question goes to the implementation.  Is this query in a stored procedure or is it hard-coded in the server-side code?  If it's hard-coded, I strongly recommend you move to a stored procedure, the script for which would look something like this:

CREATE PROCEDURE sp_Search_Query

@region nvarchar(4000) = null,
@business_type nvarchar(4000) = null,
@min_price numeric(9,2) = null,
@max_price numeric(9,2) = null

AS

SELECT *
FROM business
WHERE
(@region is not null and Region LIKE '#' + @region + '#'
AND
(@business_type is not null and Business_Type LIKE '#' + @business_type + '#')
AND
(@min_price is not null and Asking >= @min_price)
AND
(@max_price is not null and Asking <= @max_price)

GO


The advantage of this script is that it does an AND check on all criteria - thus filtering out all the data that doesn't match on all fields you request.  However, it does mean that you need to specify a range (as ActiveMedia suggested) for the Asking price.

This can be further refined - like moving the ranges, regions, and business_types to the database and sending int values to the stored procedure instead of strings (which tend to change).

If you need more help, just ask.

shovavnik

oh, btw, you would call this stored procedure thus:

sp_Search_Query N'MidWest', null, 50.25, null

This query will request all results that answer both criteria:
1. Their region is the MidWest.
2. Their cost is at least $50.25.

As you see, this method is rather flexible.

Shovanik's method has some good points but the reason I took a price range code such as "Any", "A", "B", "C", etc is that he said he wanted to have something like:
http://www.nzbizbuysell.co.nz/ 

The "price range" selection list there is something like:
  "Any"
  "$0-$150,000"
  "$150,000-$500,000"
  and so on.

These values have to be translated in some way to numeric min and max, so you may as well use a simple price category code like "A", "B", "C" for communicating with the server even though you would of  course have dollar price ranges (or "Any") showing on screen.

Null values can't normally occur in this situation.  If the visitor doesn't select anything it's interpreted as "Any".

HTH

Wow thanks for the feedback and preanswering what was going to be my next question.

Using AND in the query instead of OR solved the problen once I had included a wildcard (i.e, %) on the default labels that appeared initially in the browser.

SELECT *
FROM business
WHERE
Region LIKE '#FORM.regionMenu#'
AND Business_Type LIKE '#FORM.business_typeMenu#'
AND Asking LIKE '#FORM.askingMenu#'

Still need some help on writing the SQL for the price range values though ...

If the menu labels/values are
Any                    %
$0 - $100,000          A
$100,001 - $200,000    B

Should my SQL look something like this

FROM business
WHERE
Region LIKE '#FORM.regionMenu#'
AND Business_Type LIKE '#FORM.business_typeMenu#'
AND Asking LIKE '#FORM.askingMenu#'(BETWEEN 0 AND 100000 = 'A'BETWEEN 100000 AND 100000 = 'B')
etc

Or do I write the statement without the '#FORM.askingMenu#'. If you could give me an example of the competed SQL Id appreciate it as Im way lost on this.

Cheers,
steve

Steve257

I suggest that you need to do a simple tranlation from the values returned from the list select box on the HTML page (whether these are letter codes or actual dollar price ranges) into separate numeric minPrice and maxPrice values that can be used in the SQL.

Have a look at the previous comment I provided above on 02/22/2003 04:57AM pst.   The price codes do NOT appear in the SQL.  Remember that each record in the database table will have an ACTUAL FIGURE for a price, not a price code.

An alternative would be to have two separate dropdown list boxes in your HTML.  One called minPrice and the other called maxPrice, each of which simply returns a number.  This reduces the amount of translation you need to do and also gives the page visitor greater choice as to the exact range they are choosing.

HTH

Steve257

If you found the answers given helpful, please could you allocate the points?

This question has been classified abandoned. I will make a recommendation to the
moderators on its resolution in a week or two. I appreciate any comments
that would help me to make a recommendation.

<note>
Unless it is clear to me that the question has been answered I will recommend delete.  It is possible that a Grade less than A will be given if no expert makes a case for an A grade. It is assumed that any participant not responding to this request is no longer interested in its final disposition.
</note>

If the user does not know how to close the question, the options are here:
https://www.experts-exchange.com/help/closing.jsp


Cd&

COBOLdinosaur,

It seems that the questioner in fact asked two questions.

Both ActiveMedia and myself answered the first question.  However, based on Steve257's reply, it seems that both answers work for him.  It's only fair therefore to assign the points to the first to answer: ActiveMedia.

As to the second question, only ActiveMedia responded.  So on this count, too, it seems that the points should be alloted to ActiveMedia.

So, as this question seems to have been abandoned by the QUESTIONER, I would recommend allotting the points fairly to ActiveMedia.

shovavnik

Oh, and to use the questioner's own words:

"Wow thanks for the feedback and preanswering what was going to be my next question"

The questioner clearly feels that at least the answers given to the initial question (which is the one that counts, isn't it) are satisfactory.

I would think it appropriate to assign an A grade, if only based on the enthusiasm of the questioner's acceptance of the replies.

shovavnik