SELECT Statement using INTERSECT

Question

I have two tables: tblProduct which has list of Products, and tblConsumer which has consumer name with consumed product ID. Now I need to find the name of consumers who have consumed all products from the product table.

I tried to solve this with using INTERSECT, but the problem is I have provide each productid in WHERE clause. This syntax gives the result that I wanted, but how do I write this query where I don’t need to specify each productID.

SELECT ConsumerName  FROM tblConsumer  WHERE ProductID=  1
INTERSECT
SELECT ConsumerName  FROM tblConsumer  WHERE ProductID  =2
INTERSECT
SELECT ConsumerName  FROM tblConsumer  WHERE ProductID  =3


tblProduct
---------------------------------
ProductID  |  Product Name
---------------------------------
    1      |  Mango
    2      |  Orange
    3      |  Banana

tblConsumer
---------------------------------
ConsumerName | ProductID
---------------------------------
David        | 1
David        | 3
David        | 2
Henry        | 3
Henry        | 2

Forgive my ignorance but do you mean `UNION` as I do not know `INTERSECT` as a sql statment. Oh and please specify the DB you are using — Adrian Cornish, Aug 27 '12 at 03:42
@AdrianCornish: INTERSECT is a perfectly valid way to combine queries, PostgreSQL at least supports it: http://www.postgresql.org/docs/current/interactive/queries-union.html — mu is too short, Aug 27 '12 at 03:54
Then indeed it was my ignorance - thanks guys learned something new — Adrian Cornish, Aug 27 '12 at 03:56

Rob Farley · Accepted Answer · 2012-08-27T04:06:41.347

6

If you're actually wanting to list all the Products in tblProducts, then you can use NOT EXISTS...

Otherwise, if you have a list of the Products you want to check, you can do something like:

SELECT c.ConsumerName
FROM tblConsumer AS c
WHERE c.ProductID IN (1,2,3)
GROUP BY c.ConsumerName
HAVING COUNT(DISTINCT c.ProductID) = (SELECT COUNT(DISTINCT p.ProductID) FROM tblProduct WHERE p.ProductID IN (1,2,3))
;

But I think maybe you just want to use NOT EXISTS to eliminate the Consumers for whom there's a record they haven't bought.

Like this:

SELECT *
FROM tblPerson AS pn CROSS JOIN tblProduct AS pt /* Every possible combo */
WHERE NOT EXISTS (SELECT * FROM tblConsumer c 
                  WHERE c.ConsumerName = pn.ConsumerName 
                  AND c.ProductID = pt.ProductID)
;

edited Aug 27 '12 at 04:06

answered Aug 27 '12 at 04:00

Rob Farley

15,625
5
44
58

When I added new product called Peach into tblProduct, the query was still returing the same consumer's name. After adding new product the syntax should not return anyone's name since that additional product is not consumed by anyone. Any idea why it did not work?? – Shai Aug 27 '12 at 04:38
Because there is the WHERE c.ProductID IN (1,2,3) – András Ottó Aug 27 '12 at 04:52
1

Yeah, as András points out, I have a list of the IDs of interest in that first query. The second query is what you can use if you want the whole table, or you can remove both WHERE clauses from the first query. – Rob Farley Aug 27 '12 at 09:37
I have rewritten the suggested syntax by Rob Farley. I have removed the first WHERE clause and counting ProductID directly from tblProduct since Product ID is unique. – Shai Aug 28 '12 at 00:54
Shai - the code isn't incorrect. I mention already that you can remove the WHERE clauses if you want to use all of the possible entries. The DISTINCT in the sub-query is ignored if it is unique, so it's good practice to have it included. – Rob Farley Aug 28 '12 at 02:42

score 0 · Answer 2 · answered Aug 27 '12 at 05:07

I have an other small solution:

SELECT * FROM tblConsumer 
WHERE NOT EXISTS (SELECT * FROM tblProduct
              LEFT JOIN tblConsumer C ON tblProduct.ProductID  = C.ProductID  AND tblConsumer .ConsumerName = C.ConsumerName 
              WHERE C.ConsumerName IS NULL)

It will work if you add a new entry too. It just checks, that is there any record, where you cant make a connection between the given Consumer and a Product.

SELECT Statement using INTERSECT

2 Answers2