How to write Advance Searching SQL Query

[Fary4u]

Baically how i can differentiate Query like i need 1st Query + including 2nd part + including 3rd part & so on & so forth

SQL Query i need is like this

Residential (Not Commercial) but Tolet (Not Sale) and 2 bedrooms (Not 3) with in 40 to 70 Price (<>) Rent also include in City (Not Area) but Area (With in City chose Area) Town
EXPLAIN
i've got 4 property in DB 3 are Sale & 1 are Rented + 3 Sale Property i've got 1 in Commercial & 2 in Residential Property + 2 Residential one's i've got 1 in x area & 2 in y area (but Y is Commercial) is these both are in some city & 1 in some other city + 1 is less then 1000 & 1 is greater then 1000

these filed i've got in database, it's taking values from Dropdown + Combo box + Radio & Text filed

Here is Coding i'm writing database fields as well but this coding is working but not detail search.

chkres = Request.form("chkres")		 ' ( DB Filed as Type - Resd, Comcl, Othr )
chkcom = Request.form("chkcom")	 ' ( DB Filed as Type - Resd, Comcl, Othr )
chkoth = Request.form("chkoth")		 ' ( DB Filed as Type - Resd, Comcl, Othr )
strCat = Request.form("strCat")		 ' ( DB Filed as Category_ID - Sale or Tolet )
intPrice = Request.form("intPrice") 	 ' ( DB Filed as Price comprasion Min )
intPricem = Request.form("intPricem") 	 ' ( DB Filed as Price compras  Max )
strbed = Request.form("strbed")		 ' ( DB Filed as Beds - 0 <> 1 , 2 , 3 )
strText = cstr(Request.form("strText")) '(DB Filed as city & town Text Search)
			
	if chkres = "yes" then
		chkres = "Residential"
	end if
	if chkcom = "yes" then
		chkcom = "Commercial"
	end if
	if chkoth = "yes" then
		chkoth = "Others"
	end if	

        strTextWhere = " and (uCase(db_city) like '%"&strText&"%'"
        strTextWhere = strTextWhere & " or uCase(db_town) like '%"_
            & strText & "%')"    

'  intPriceWhere = " and Price < " &intPrice
   intPriceWhere = " and Price >= " & intPrice & " and Price <=" & intPricem 

        strCatWhere = " and property.category_ID = '" &strCat&"'"
        
        strChk1 = " and property.Type = '" &strChk1&"'"			
'        strChk2 = " and property.Type = '" &strChk2&"'"		
'        strChk3 = " and property.Type = " & strChk3	' Check box options

        strbed = " and property.Beds < '" &strbed&"'"

    set Conn = Server.CreateObject("ADODB.Connection")
    Conn.Open ConString

    sqlText = "select distinct(property.property_ID), " 
    sqlText = sqlText & "Property.Price ,"
    sqlText = sqlText & "Property.Style ,"
    sqlText = sqlText & "Property.Category_ID ,"
    sqlText = sqlText & "Property.Beds ,"    
    sqlText = sqlText & "Property.address ,"    
    sqlText = sqlText & "Property.offer ,"    
    sqlText = sqlText & "Property.Image_1 ,"    
    sqlText = sqlText & "Property.Type "
    sqlText = sqlText & "from property " 
    sqlText = sqlText & "where property.property_ID = "
    sqlText = sqlText & "property.property_ID"
sqlText = sqlText & strCatWhere & strTextWhere & intPriceWhere & strChk3 & etc.

it's quite completed query if some body help me to solve this i'm realy thx.
Hope u get my point.

[Fary4u]

Hi i'm just can't sort it out can u plz look the coding give me the possiblity reply

[Fary4u]

What the simplest way to run this kind of problem ?

[jhardman]

What the simplest way to run this kind of problem ?

Instead of replying to bump your post, please consider sending a PM to an forum expert. In answer to your question, I often form complex queries like this:[code=asp]query = "SELECT * FROM properties WHERE "
if request("maxPri ce") <> "" then
query = query & "Price <= " & request("maxPri ce") & " AND "
end if

if request("minPri ce") <> "" then
query = query & "Price >= " & request("minPri ce") & " AND "
end if

if request("tolet" ) <> "" then
query = query & "tolet = 'True' AND "
end if

'etc. Notice the where clause of the query ends with " AND " no matter what

'you need to trim the " AND " off the end
query = left(query, len(query)-5)[/code]Let me know if this helps.

Jared

[jhardman]

Right before you send this query to the db, check to see what the final output is:
[code=asp]response.write "Query: " & query & "<br>" & vbNewLine[/code]printing out this query is a good place to start troubleshooting . If you don't see the problem just by looking, you can copy the query into a SQL query analyzer which will tell you if you got the right answer.

Please print out this query and then tell me what you get.

Jared