Write a SQL query that returns active products containing every tag supplied in `@SelectedTags`.

Tables: Products(ProductId, Name, IsActive) and ProductTags(ProductId, TagId). The table-valued parameter @SelectedTags(TagId) may contain zero or more rows. Return a product only when all distinct selected tags are attached to it. An empty selection returns every active product.

Selected tags are 2 and 5. Product A has tags 2, 5, and 8, so it matches. Product B has only tag 2, so it does not. With no selected tags, both active products match.

• Treat duplicate selected tag ids as one requested tag.
• Extra product tags do not prevent a match.
• Inactive products never appear.
• The empty selection rule must be explicit.

I first count distinct requested tags. For a non-empty selection, I join product-tag rows to the selected ids, group them by product, and require the number of distinct matches to equal the requested count. The outer EXISTS keeps the product result to one row. The separate zero-count branch implements the product decision that no filter means all active products.

DECLARE @SelectedCount int =
(
    SELECT COUNT(DISTINCT TagId)
    FROM @SelectedTags
);

SELECT p.ProductId, p.Name
FROM dbo.Products AS p
WHERE p.IsActive = 1
  AND
  (
      @SelectedCount = 0
      OR EXISTS
      (
          SELECT 1
          FROM dbo.ProductTags AS pt
          INNER JOIN @SelectedTags AS st ON st.TagId = pt.TagId
          WHERE pt.ProductId = p.ProductId
          GROUP BY pt.ProductId
          HAVING COUNT(DISTINCT pt.TagId) = @SelectedCount
      )
  )
ORDER BY p.ProductId;

A unique index on ProductTags(ProductId, TagId) protects the model and supports each product probe. For large selections or catalogs, the optimizer may prefer starting from selected tags and grouping matching product ids before joining products.

• No selected tags.
• Duplicate rows in @SelectedTags.
• A product with all selected tags plus additional tags.
• Duplicate product-tag rows in a legacy table.

• Repeat a selected tag to catch comparisons against the raw input count.
• Use one product that matches only a subset.
• Use an empty table-valued parameter.
• Add extra tags to a matching product.

• Using IN and returning products that match any selected tag.
• Comparing counts without deduplicating the selected ids.
• Returning no products for an empty selection by accident.
• Joining directly to the final result and returning duplicate products.