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

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

Filtering table data based on multiple conditions from sql server

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Filtering table data based on multiple conditions from sql server

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us