Hi all,
i have made a SEARCH page with the following inputs:

KEY
WARD
BLOCK
ZONE
TITLE
SUBT

I need to make a query such that whereever the user enters data, result comes, be it 1 input, all inputs or combination of inputs.
This is working in all ipput fields, except for KEY & WARD. They are showing all records & not filtering as what is being selected.

The query is written below, plz. suggest me something.

-- shows comment

SELECT DISTINCT
FROM blah blah blah................
WHERE
--CONTAINS(viewname.*,@SKey)
(
(c.block LIKE '%' + @SKey +'%') or (c.zone LIKE '%' + @SKey +'%')
)

or

-- CONTAINS (c.*, '*@ward*')
((c.ward = @ward) or (ward <> ''))
--- (c.ward LIKE '%' + @ward +'%')
AND
-- (@block = '') OR (c.block LIKE '%' + @block +'%')
--(c.block = @block )
(c.block LIKE '%' + @block +'%')

--AND ((@zone = '') OR (c.zone = @zone)
AND( c.zone LIKE '%' + @zone +'%')
-- or (@zone = '')

--AND ((@title = '') OR ( c.co_title = @title)
AND (c.title LIKE '%' + @title +'%')
-- or (@title = '')
--AND ((@subt = '') OR (c.co_subt = @subt)
AND (c.subt LIKE '%' + @subt +'%')
--or (@subt = '')

I feel i need to place the ORs and ANDs properly.

Plz. help me out!
Regards
Monica

Hi Monica,

You might already know this. I don't know what the datatype for any of these fields are but if you use LIKE with some kind of number/integer then if you select a 1, the recordset will include any integer that has a 1 in it... and therefore possibly not filter like you want it to.
c.ward will either equal your selected value OR it will pull everyting where its not equal to nothing

So it seems that your ward statement will not filter at all unless there are fields without values.

Hi,

    Thanks for your reply. All variables are of varchar data type. I feel some manipulation of ORs/ANDs is required. I have tried it but it gives weired results.

Regards
Monica