Intersect Select Statements on Specific Columns

Question

I've a table of SalesDetails, looking like this:

InvoiceID, LineID, Product

1,1,Apple
1,2,Banana
2,1,Apple
2,2,Mango
3,1,Apple
3,2,Banana
3,3,Mango

My requirement is to return rows where an Invoice contained sales of both: Apple AND Banana, but if there are other products on such an invoice, I don't want those.

So the result should be:

1,1,Apple
1,2,Banana
3,1,Apple
3,2,Banana

I tried the following:

Select * from SalesDetails where Product = 'Apple'
Intersect
Select * from SalesDetails where Product = 'Banana'

Didn't work, because it seems Intersect needs to match all the columns.

What I'm hoping to do is:

Select * from SalesDetails where Product = 'Apple'
Intersect ----On InvoiceID-----
Select * from SalesDetails where Product = 'Banana'

Is there a way to do this?

Or do I have to first Intersect on InvoiceIDs only using my criteria, then select the rows of those InvoiceIDs where the criteria is matched again, I.e.:

Select * From SalesDetails
Where Product In ('Apple', 'Banana') And InvoiceID In 
  (
  Select InvoiceID from SalesDetails where Product = 'Apple'
  Intersect
  Select InvoiceID from SalesDetails where Product = 'Banana'
  )

Which seems somewhat wasteful as it's examining the criteria twice.

Answer 1

Okay this time I've managed to get reuse of the Apple/Banana info by using a CTE.

with sd as (
Select * from SalesDetails 
where (Product in ('Apple', 'Banana'))
)
Select * from sd where invoiceid in (Select invoiceid from 
  sd group by invoiceid having Count(distinct product) = 2)

SQL Fiddle

Answer 2

Do it with conditional aggregation:

select * 
from SalesDetails
where product in ('apple', 'banana') and invoiceid in(
select invoiceid
from SalesDetails
group by invoiceid
having sum(case when product in('apple', 'banana') then 1 else 0 end) >= 2)

Answer 3

I think OP's suggestion is about the best one can do. The following might be faster, although I expect the difference to be slight and I have not done any benchmarking.

Select * From SalesDetails
Where Product ='Apple' And InvoiceID In
(
Select InvoiceID from SalesDetails where Product = 'Banana'
)
union all
select * from SalesDetails
Where Product ='Banana' And InvoiceID In
(
Select InvoiceID from SalesDetails where Product = 'Apple'
)

Answer 4

A self-join will solve the problem.

SELECT T1.*
FROM SalesDetails T1 
INNER JOIN SalesDetails T2 ON T1.InvoiceId = T2.InvoiceId
   AND (T1.Product = 'Apple' AND T2.Product = 'Banana'
     OR T1.Product = 'Banana' AND t2.Product = 'Apple')