1
Order
-----
OrderId

OrderDetail
-----------
OrderDetailId
OrderId
ProductId

How do I write a query that returns all orders that contain a specific product without using a sub-query?

I don't want to write a sub-query because I want to encapsulate the query in a view and use a WHERE clause on the view to supply the product id instead of having a stored procedure and supplying a parameter.

I figure the only way to do this is to join orders to details and GROUP BY on order but even then I don't know how to have the aggregate column resolve to whether the product is amongst the order details.

  • I don't understand why a subquery is out of the question. How does it restrict you? Can you give an example of the code you tried (and did not work)? – ypercubeᵀᴹ Mar 3 '15 at 15:16
  • 1
    Do you mean you want to use the view with something like SELECT * FROM Orders_without_a_product_View WHERE product_id = X; ? – ypercubeᵀᴹ Mar 3 '15 at 15:18
  • Yes exactly. I don't see how to get a product id outside the view into the query. – Ian Warburton Mar 3 '15 at 15:19
3

I have no idea about efficiency* but a view like this:

CREATE VIEW 
    Orders_with_a_product_View AS
SELECT 
    p.ProductID, o.*
FROM
    dbo.Product AS p
  JOIN
    dbo.[Order] AS o
      ON EXISTS
         ( SELECT 1
           FROM dbo.OrderDetail AS od
           WHERE od.OrderID = o.OrderID
             AND od.ProductId = p.ProductId
         ) ;

would allow you to use it with:

SELECT * 
FROM Orders_with_a_product_View 
WHERE ProductID = X ;

(*Regarding efficiency: Hopefully the optimizer will "push-down" the condition and not do a cross join of all products and orders.)


It might be better to follow a simpler road, like this:

CREATE VIEW 
    Order_Product_IDs AS
SELECT DISTINCT 
    ProductID, OrderID
FROM
    dbo.OrderDetail ;

and the utilize it with:

SELECT o.* 
FROM dbo.Order_Product_IDs AS op 
  JOIN dbo.[Order] AS o
    ON o.OrderID = op.OrderID
WHERE op.ProductID = X ;
  • A join on an exists. That's amazing. – Ian Warburton Mar 3 '15 at 15:38
  • I assumed that (OrderID, ProductID) is not unique in OrderDetails. Otherwise, a simple join would be enough (and the Product table unneeded). – ypercubeᵀᴹ Mar 3 '15 at 15:39
  • The optimizer is only using the product id to seek in the products table. Maybe I can jig it to do something smarter. (OrderID, ProductID) are in fact not unique. Some attributes further distinguish products with the same id. – Ian Warburton Mar 3 '15 at 15:44
  • I will try your second query. The first works with index seeks until I swap out the order table for an order view at which point the optimizer seems to lose touch with the ability to use the product id smartly. The query goes from 1 second to 7. – Ian Warburton Mar 3 '15 at 17:18
  • I'd think an index on (ProductID, OrderID) would be helpful. Do you have such an index? – ypercubeᵀᴹ Mar 3 '15 at 17:20
1

You could create a table-valued function like this:

CREATE FUNCTION dbo.OrdersWithProduct (@ProductId int)
RETURNS TABLE
AS
RETURN
(
  SELECT
    o.*
  FROM
    dbo.[Order] AS o
  WHERE EXISTS
    (
      SELECT
        *
      FROM
        dbo.OrderDetail AS od
      WHERE
        od.OrderId = o.OrderId
        AND od.ProductId = @ProductId
    )
);

And then use it to find orders containing one specific product:

SELECT
  *
FROM
  dbo.OrdersWithProduct(@SomeProductId)
;

or any of a list of specific products:

SELECT
  p.ProductId,
  f.OrderId
FROM
  dbo.MyCustomProductList AS p
  CROSS APPLY dbo.OrdersWithProduct(p.ProductId)
;

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.