SQl Help - Get Only 1 product

JRockFL
JRockFL used Ask the Experts™
on
How can I return only the orders that have 1 item with a productid of 46?
This query will return orders that contain multiple products including 46.
I want only orders that have 1 product and that has productid of 46

select *
from Orders a
inner join LineItems b
on a.OrderID = b.OrderID
and b.ProductID = 46
where a.OrderStatusID = 3
and a.GatewaySuccessful = 1
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
Commented:
select *
from Orders a
inner join LineItems b
on a.OrderID = b.OrderID
and b.ProductID = 46
where a.OrderStatusID = 3
and a.GatewaySuccessful = 1 and not exists
    (select 1
    from lineitems b2
    where b2.orderid = b.orderid and b2.productid <> 46)

Author

Commented:
Awesome!! Thank you!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial