question

James K avatar image
James K asked

Filter by Location - design & how to ?

Background - company sells products nationally. requirement is to limit products to whole country, to certain states, or to certain cities. Products may have multiple limits, so it may be available to all of the state of North Carolina and to Boston, Mass (but no other city in Mass.). Stumped on ideas for design of table to hold this and how to join products to it.

TblProducts (ProductID int, ProductName VarChar(100))

TblFilter (ProductID int, State Char(2), City VarChar(250))

with a table setup like that, what kind of query would return all the products if you knew the state and city? Is there a better table design?

sql-server-2005
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Kristen avatar image
Kristen answered
            
SELECT P.ProductID, P.ProductName            
FROM TblProducts AS P            
JOIN TblFilter AS F            
ON F.ProductID = P.ProductID            
WHERE F.State = @UserState            
AND (F.City IS NULL OR F.City = @UserCity)            

My thinking is that to make a product available for the whole state you set F.City to NULL; otherwise you create rows in TblFilter for each individual City that is allowed to buy the product.

You could do a similar thing for products available nationally - set F.State to NULL to indicate that and then change to

WHERE (F.State IS NULL OR F.State = @UserState)

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.