CODEWITHSUNDEEP
sqloracle-databaseselectintersect
Shai
Shai

Reputation: 569

SELECT Statement using INTERSECT

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

Upvotes: 2

Views: 468

Answers (2)

András Ottó
András Ottó

Reputation: 7695

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.

Upvotes: 0

Rob Farley
Rob Farley

Reputation: 15849

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)
;

Upvotes: 6

Related Questions