Sql-server – Multiple filters with joins

sql server

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?

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