Return orders containing at least one line for a requested product without returning an order more than once.

Tables: Orders(OrderId, CustomerId, CreatedAt, Status) and OrderLines(OrderLineId, OrderId, ProductId, Quantity). Return each non-cancelled order containing @ProductId, newest first. Use OrderId as the tie-breaker.

Order 10 contains the requested product on two separate lines. It still appears once. Order 11 has no matching line and does not appear.

• Do not use DISTINCT as a patch for an unnecessary row-multiplying join.
• Exclude cancelled orders.
• Ordering must be deterministic.

Because no line columns are needed in the result, I model the related table as an existence condition. The correlated subquery answers yes or no for each order and cannot multiply the order row. A composite index on OrderLines(ProductId, OrderId) supports starting from the selective product filter; the reverse order can be better when the query normally starts from a small page of orders.

SELECT o.OrderId, o.CustomerId, o.CreatedAt, o.Status
FROM dbo.Orders AS o
WHERE o.Status <> 'Cancelled'
  AND EXISTS
  (
      SELECT 1
      FROM dbo.OrderLines AS ol
      WHERE ol.OrderId = o.OrderId
        AND ol.ProductId = @ProductId
  )
ORDER BY o.CreatedAt DESC, o.OrderId DESC;

The best index order depends on whether product selectivity or the outer order page drives the plan. The important shape is a semi-join: one qualifying match is enough, and no deduplication step is required.

• The same product appears on multiple lines in one order.
• An order has many non-matching lines and one matching line.
• Two orders share the same CreatedAt.
• A cancelled order contains the product.

• Insert duplicate product lines for one order.
• Verify tie-breaking when timestamps match.
• Use a product that appears only in cancelled orders.

• Joining lines and returning duplicate order rows.
• Adding DISTINCT without understanding why duplicates appeared.
• Selecting line data when only existence is required.
• Ordering only by a non-unique timestamp.