0

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?

  • 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
1

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)
)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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