T-SQL – How to Query Orders Containing a Product Without Sub-Query

t-sql

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.

Best Answer

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 ;
Related Question