Filtering table data based on multiple conditions from sql server

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

I have 6 listboxes and three date textboxes which I'm using to apply conditional filtering on a database table. What I need is if I select only a single textbox or a listbox value, or multiple listbox values and multiple date values I should be able to retrieve data based on only selected filter conditions from front end. The date or listbox values that I don't select should be skipped.

I have the following query but its not working.How should i write the query?

SQL

SELECT (MPMST.partyname + '' + ',' + '' + MPMST.contactperson) AS NAME
      ,(MPMST.email + '' + ',' + '' + MPMST.phone) AS ContactDetail
      ,MPMST.address
      ,(MPMST.city + '' + ',' + '' + MPMST.area) AS city_area
      ,MPMST.Already_Computer
      ,MPMST.software
      ,visit_time
      ,visit_purpose
      ,interaction
FROM    MPMST
INNER
JOIN    MCall   ON  MPMST.Partycode = MCall.Partycode
WHERE  MCall.calltype IN ('SUP001')
AND MPMST.area IN ('pavai')
AND MPMST.city  IN ('mumbai')
AND MCall.Date >= CONVERT(DATETIME ,'01/01/2014' ,103)
AND MCall.Date <= CONVERT(DATETIME ,'31/01/2014' ,103) MCall.status = ''
AND MPMST.software = ''
AND MPMST.Software <> ''
AND MPMST.City IS NOT NULL
AND MPMST.Area IS NOT NULL

my previous query was like

SQL

WHERE MCall.calltype IN ('SUP001') OR MPMST.area IN ('pavai') OR MPMST.city IN ('mumbai') AND OR MCall.status = '' OR MPMST.software = '' AND MPMST.Software <> '' AND MPMST.City IS NOT NULL AND MPMST.Area IS NOT NULL

..this query gave me only the records satisfying the first condition i.e..'Sup001'

Posted 17-Jan-14 20:54pm

pwavell

Updated 17-Jan-14 21:54pm

Nelek

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

er.rakesh · Answer 1 · 2014-01-17T21:56:00

Solution 1

slightly modify your where condition like :
WHERE (cond1 or cond2)
and (cond3 or cond4)
and (cond5 or cond6)

if there would be any issue plz do let me know

Posted 17-Jan-14 21:56pm

er.rakesh

Comments

pwavell 18-Jan-14 4:40am

not working.if the query receives only one filter condition it should fetch rows satisfying that condition.if i give two or more it should fetch data like
where condn1 and condn2 and condn3 and so on.if i dont aapply any condn then it should return all the records between from and to dates

er.rakesh 18-Jan-14 4:43am

plz give me your where condition clause

Christian Graus · Answer 2 · 2014-01-18T17:24:00

Solution 2

WHERE MCall.calltype IN ('SUP001') OR MPMST.area IN ('pavai') OR MPMST.city IN ('mumbai') AND OR MCall.status = '' OR MPMST.software = '' AND MPMST.Software <> '' AND MPMST.City IS NOT NULL AND MPMST.Area IS NOT NULL

The answer someone gave you is right. 'not working' does not help. The issue is very simple - SQL has an order of operations ( that is, order in which it applies things ) and it's not what you're assuming. Be VERY careful with the 'OR' keyword. Make sure all your conditions are in brackets so that which groups are to be ORed is explicit.

WHERE
(MCall.calltype IN ('SUP001') OR
MPMST.area IN ('pavai') OR
MPMST.city IN ('mumbai') )
AND
(MCall.status = '' OR MPMST.software = '')
AND MPMST.Software <> '' AND MPMST.City IS NOT NULL AND MPMST.Area IS NOT NULL

is very different to

WHERE
MCall.calltype IN ('SUP001') OR
MPMST.area IN ('pavai') OR
(MPMST.city IN ('mumbai') AND (MCall.status = '' OR MPMST.software = ''
AND MPMST.Software <> '' AND MPMST.City IS NOT NULL AND MPMST.Area IS NOT NULL)

For example.

Posted 18-Jan-14 17:24pm

Christian Graus

Comments

Liberty Crown Infotech 27-Oct-16 7:45am

I have a textbox and 3 dropdown boxes with an item that could be selected in each of the dropdowns. There are two filtering scenarios I want to be able to achieve. 1.Getting a filtering result after typing a value in the textbox and select values in the next two textboxes leaving out the last one. 2.Getting a filtering after typing a value in the textbox and select values in the other three dropdown boxes to filter out the result. My code below can only produce each of the results one at a time. Can anyone help with modification or new query to be able to achieve the two scenarios?

CREATE proc spSearchProduct
@searchWord1OnMasterPage nvarChar(50),
@searchWord2OnMasterPage nvarChar (50),
@searchWord3OnMasterPage nvarChar (50),
@searchWord4OnMasterPage nvarChar (50)
as
Begin
Select Product.Name,Price,Seller,ProductStreetNo.StrNo,ProductStreet.StreetName from Product
INNER JOIN ProductStreetNo
ON Product.StreetNoID = ProductStreetNo.IDStreetNo
INNER JOIN ProductStreet
ON Product.StreetID = ProductStreet.IDStreet
INNER JOIN ProductState
ON StateID=ProductState.IDState
INNER JOIN ProductCity
ON CityID=ProductCity.IDCity
where

Product.Name Like '%' + @searchWord1OnMasterPage + '%' and

ProductState.StateName Like '%' + @searchWord2OnMasterPage + '%' and

((ProductCity.CityName Like '%' + @searchWord3OnMasterPage + '%' )or
(ProductStreet.StreetName Like '%' + @searchWord4OnMasterPage + '%') or
(ProductStreet.StreetName is null)) and

((ProductCity.CityName Like '%' + @searchWord3OnMasterPage + '%' )or
(ProductStreet.StreetName Like '%' + @searchWord4OnMasterPage + '%') or
(ProductStreet.StreetName is null))

End

Asad Ali May2022 24-May-22 6:53am

Have you got its solution?? if you have then please share with me on my whatsapp +923055100983. thank you