Finding Orders With One Product And One OTHER Product From Another Category

Question

Let's say I have a database where the relevant portions look like this:

PRODUCT TABLE

productid

categoryid

ORDER LINE ITEMS TABLE

orderid

productid

Let us also say that productid #1 is in category #1.

My goal is to find all orders that have BOTH a line item any product in categoryid #2 AND productid #1 (which is not in categoryid #2). In other words, a list of all orderids where the customer's ordered both anything from categoryid #1001 AND product #1.

I'm sure there's an easy fix for this, but I'm blanking on it right now - all of my ANDs and ORs are getting tangled up, excluding one or the other!

Answer 1

Assuming you have a table named ORDER:

select o.* --put actual column names here instead of *
from ORDER o
where o.orderid in (
 select orderid
 from PRODUCT p 
 inner join [ORDER LINE ITEMS] ol on p.productid = ol.productid
     and p.categoryid = 2
)
and o.orderid in (
 select orderid
 from [ORDER LINE ITEMS] ol
 where ol.productid = 1
)