2

I have a table called Orders which consists of columns CUSTNUM, PRODNUM, DATE and QTY.

I am trying to retrieve the customers who ordered both products 3737 and products 9193 (particular values)

So I tried the following code with no success:

SELECT CUSTNUM
FROM ORDERS
WHERE PRODNUM = 9193

INTERSECT

(SELECT CUSTNUM
FROM ORDERS
WHERE PRODNUM = 3737);

After further reading also on this forum, I also tried this:

SELECT DISTINCT CUSTNUM
FROM ORDERS
WHERE PRODNUM IN (
select PRODNUM FROM ORDERS WHERE PRODNUM = 3737
) AND PRODNUM IN (
Select PRODNUM FROM ORDERS WHERE PRODNUM = 2357
);

The INTERSECT operator seems not to work although it is included in our textbooks.

I've also consulted INTERSECT in MySQL

Community
  • 1
  • 1
Alex Cutajar
  • 29
  • 5
  • "So I tried the following code with no success". You can hardly be less specific as to what's happening. You are getting customers that bought only one of the products? You are getting completely unrelated customers? The query runs for weeks? You are getting memory issues? Please always tell us *what* happens. In your case you should get an error message indicating that `INTERSECT` doesn't exist in MySQL. And given such error message, what might be your question? – Thorsten Kettner Dec 27 '16 at 11:49
  • Yes, I am getting that INTERSECT does not exist in the first piece of code.. thus I am trying the second which is not giving any results for some reason – Alex Cutajar Dec 27 '16 at 15:29

1 Answers1

2

MySQL supports UNION [ALL] but not MINUS/EXCEPT and INTERSECT.

select    custnum            
from      orders    
where     prodnum in (3737,9193)    
group by  custnum       
having    count(distinct prodnum) = 2

or

select    custnum            
from      orders    
where     prodnum in (3737,9193)    
group by  custnum       
having    min(prodnum) = 3737
      and max(prodnum) = 9193
David דודו Markovitz
  • 42,900
  • 6
  • 64
  • 88