Help to write search query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help to write search query

Author

Topic

newtosql_pk
Starting Member

1 Post

Posted - 2009-07-03 : 10:30:48

I need help in creating an effective SQL query for the scenario below:

The user can search on any of the following fields:
field1,field2,field3,field4,field5,field6 (asp fields matching to col1...col6 in sql server table)

eg table:
col1 col2 col3 col4 col5 col6 searchtype
_________________________________________________________
1 0 a b x d c
1 123 x d df gf d
2 0 a d r gf c
2 2434 s dg rt dfg d
__________________________________________________________
NOTE:Seacrh type 'c' are the master records to be considered whatever be the search criteria. There are 2 rows for each col1 one with search type='c' & 1 with searc type='d'

In sql table:
col 2 can have 2 possible values '0' or 'any valid number' in table
Search on field 2 can be on spaces,'0','valid numeric value'

search on other fields can be on spaces or any valid value

Case1:I have written the following query in ASP for case when col2=0

If ((trim(field1) <> "") or (trim(field6) <> "") or (trim(field3) <> "") or (trim(field4) <> "") or (trim(field5) <> "")) and (trim(field2) = "0") then
strSQL="select col1,col2,col3,col4,col5,col6 from tablename where SearchCode='C' AND "
strSQL=strSQL & " (col1) Like '%" & field1 & "%' AND (col6) Like '%" & field6 & "%' AND (col3) Like '%" & col3 & "%') AND (col4) Like '%" & col44 & "%' AND (col5) Like '%" & col5 & "%'" & ";"

and the following query in ASP when col2 !='0' or spaces
Case2: Query2:
If ((trim(field2) <> "0") and (trim(field2) <> ""))or (trim(field1) <> "") or (trim(field6) <> "") or (trim(field3) <> "") or (trim(field4) <> "") or (trim(field5) <> "") then
Response.Write "<br> im in dp query <br>"
strSQL="select col1,col2 from table where "
strSQL=strSQL & " (col1) Like '%" & field1 & "%' AND (col2) Like '%" & field2 & "%' AND (col6) Like '%" & field6 & "%' AND ((col3) Like '%" & field3 & "%') AND (col4) Like '%" & col4 & "%' AND (col5) Like '%" & col5 & "%'" & ";"

If query 2 is successful,then

strSQL2="select select col1,col2,col3,col4,col5,col6 from tablename where SearchCode='C' AND col1" & col1 from query2

QUESTION:Can there be a combined query to allow this search?
I have written 2 queries for the case,if col2 !=spaces & is not zero then search for col1 with searchcode='c' & then using this value of col1,search all the other fields

If no what is the correct query,my queries are not giving correct results

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources