Click here to Skip to main content
16,019,359 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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 If I change the "and" to "or" and the "or"s to "and"s. Can anyone help with modification or new query to be able to achieve the two scenarios?

What I have tried:

SQL
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
Posted
Updated 27-Oct-16 3:39am
v2

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

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

(ProductCity.CityName Like '%' + @searchWord3OnMasterPage + '%' )and


((ProductStreet.StreetName Like '%' + @searchWord4OnMasterPage + '%') or
(@searchWord4OnMasterPage = 'Select Street'))
 
Share this answer
 
Change In Query
** Please See Carefully

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
(
(@searchWord1OnMasterPage IS NULL)
OR
(Product.Name Like '%' + @searchWord1OnMasterPage + '%' )
)
and
(
(@searchWord2OnMasterPage IS NULL)
OR
(ProductState.StateName Like '%' + @searchWord2OnMasterPage + '%')
)
and
(
(@searchWord3OnMasterPage IS NULL)
OR
(ProductCity.CityName Like '%' + @searchWord3OnMasterPage + '%' )
)
and
( (@searchWord4OnMasterPage IS NULL)
OR
(ProductStreet.StreetName Like '%' + @searchWord4OnMasterPage + '%')
)
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900