CODEWITHSUNDEEP
sql-servert-sql
UserSN
UserSN

Reputation: 1013

Combining multiple WHERE clauses

I have the following select statement that is working for me.

select * 
from BND_listing right 
join BND_ListingCategories on BND_Listing.CatID = BND_ListingCategories.CatID
where 
    (CategoryName = '[querystring:filter-Category]' or 
     '[querystring:filter-Category]' = 'All')
    and (City = '[querystring:filter-City]' or 
         '[querystring:filter-City]' = 'All')
    and (Region = '[querystring:filter-State]' or 
         '[querystring:filter-State]' = 'All')
    and (Country = '[querystring:filter-Country]' or 
         '[querystring:filter-Country]' = 'All')
    and isnull(Company,'') <> ''
order by 
    Company asc

In addition to what it's doing I'd like to include the following WHERE clause that basically just allows me to create a "Search Box"

DECLARE @param VARCHAR(MAX)
SET @param = '[querystring:searchterm]'

SELECT Company
FROM BND_Listing
WHERE Company LIKE '%' + @param + '%'

Is it possible to combine both of these select statements into one and retain all the functionality?

Upvotes: 0

Views: 59

Answers (1)

tuxmania
tuxmania

Reputation: 956

DECLARE @param VARCHAR(MAX)
SET @param = '[querystring:searchterm]'


select * 
from BND_listing right 
join BND_ListingCategories on BND_Listing.CatID = BND_ListingCategories.CatID
where 
    (CategoryName = '[querystring:filter-Category]' or 
     '[querystring:filter-Category]' = 'All')
    and (City = '[querystring:filter-City]' or 
         '[querystring:filter-City]' = 'All')
    and (Region = '[querystring:filter-State]' or 
         '[querystring:filter-State]' = 'All')
    and (Country = '[querystring:filter-Country]' or 
         '[querystring:filter-Country]' = 'All')
    and isnull(Company,'') <> ''
    AND Company LIKE '%' + @param + '%'
order by 
    Company asc

Upvotes: 1

Related Questions