Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I just read this qestion: SQL Multiple Filter - Optimal Way

I tried this with Northwind. I filter like this:

declare @supplier int = 1
declare @category int = 1
select * from products p 

WHERE (@supplier  = '' or SupplierId = @supplier)
  AND (@category = '' or CategoryId = @category)

This seems to work as expected, I get two hits: Chai and Chang. Now I want to add filter ShipPostalCode:

declare @supplier int = 1
declare @category int = 1
declare @shippostal nvarchar(50) = '01307'
select * from products p
inner join [Order Details] od on p.ProductId = od.ProductId
inner join Orders o on o.OrderId = od.OrderId
AND (@category = '' or CategoryId = @category)
AND (@shippostal = '' or ShipPostalCode = @shippostal)

Because of the join I now have multiple rows of Chai and Chang. These filters are users who makes selection in dropdown lists, so even if shippostalcode is chosen, I still need one hit.

distinct productName

will give me one row per product, but that's not the way to solve it, is it?

EDIT: Thank you guys, Yes, supplier is an int.

So If there be would another many to many relation from the Products table I can just add another AND, I guess.

Sorry, this is a very sad example, but if I add OrderDetails2 and Orders2 where OrdersDetails2 will be a junction table between Products and Orders2, I can just add add another AND (and keep on filtering), like this:

declare @supplier int = 1
declare @category int = 1
declare @shippostal nvarchar(50) = '01307'
declare @city nvarchar(50) = 'boston'
select * from products p 
WHERE (@supplier  = '' or SupplierId = @supplier) AND (@category = '' or CategoryId = @category)
AND ProductId in
(
Select od.ProductId from [Order Details] od  
inner join Orders o on o.OrderId = od.OrderId
Where (@shippostal = '' or ShipPostalCode = @shippostal)
)
AND ProductId in
(
Select od.ProductId from OrderDetails2 od  
inner join Orders2 o on o.OrderId = od.OrderId
Where (@city = '' or City = @city)
)

As you can see, the Order2 table has a column 'City' What do you think?

share|improve this question
    
IN or EXISTS subquery is what would solve this, if you want results only from the products table. Irrelevant: Why are you using WHERE @supplier = '' when @supplier is an integer? –  ypercube Sep 22 '13 at 10:52
add comment

1 Answer

You could look for ProductIds contained in a subselect

declare @supplier int = 1
declare @category int = 1
declare @shippostal nvarchar(50) = '01307'

select * from products p 
WHERE (@supplier  = '' or SupplierId = @supplier) AND (@category = '' or CategoryId = @category)
AND ProductId in
(
Select od.ProductId from [Order Details]  
inner join Orders o on o.OrderId = od.OrderId
Where (@shippostal = '' or ShipPostalCode = @shippostal)
)
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.