SQl Help - Get Only 1 product

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
LVL 8
JRockFLAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor 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)
0
 
JRockFLAuthor Commented:
Awesome!! Thank you!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.