SQL filter entries which match all entries from another table

Question

I am using MySQL. Let's say I have these two tables:

table 1
+---------+
| product |
+---------+
|       1 |
|       2 |
+---------+

table2
+------+---------+
| name | product |
+------+---------+
| A    |       1 |
| A    |       2 |
| B    |       1 |
| B    |       3 |
| C    |       1 |
+------+---------+

which are produced using the following code:

CREATE TABLE table1(
    product INT
);

CREATE TABLE table2(
    name VARCHAR(10),
    product INT
);

INSERT INTO table1 VALUES(1);
INSERT INTO table1 VALUES(2);

INSERT INTO table2 VALUES('A', 1);
INSERT INTO table2 VALUES('A', 2);
INSERT INTO table2 VALUES('B', 1);
INSERT INTO table2 VALUES('B', 3);
INSERT INTO table2 VALUES('C', 1);

I would like to produce a table with names from table2, for which its products match all products of table1. In this case, simply

+------+
| name |
+------+
| A    |
+------+

That's the name of the retailer for which all products match the ones in the other table.

This is probably something simple that I am failing to see. I have tried inner joins, using all with a subquery, ... but...

I already referred some command I attempted to use. I will post later my attempts when I am on the computer. I haven't posted those before because I figured this was something really simple, and I thought I was just going to pollute the post with my half-baked attempts. But I can post them, sure. — Ricardo Magalhães Cruz, Oct 12 '18 at 07:14

score 1 · Answer 1 · answered Oct 13 '18 at 00:00

1

I ended up being able to solve this using:

SELECT nome
FROM table2
WHERE product IN (SELECT product FROM table1)
GROUP BY nome HAVING COUNT(*) = (SELECT COUNT(*) FROM table1);

Based on check if a column contains ALL the values of another column - Mysql

answered Oct 13 '18 at 00:00

Ricardo Magalhães Cruz

3,504
6
33
57

score 0 · Answer 2 · answered Oct 11 '18 at 23:11

0

You can use a join to get any matches. And then having to check they are all there.

Assuming no duplicates:

select t2.name
from table2 t2 join
     table1 t1
     using (product)
group by t2.name
having count(*) = (select count(*) from table1);

answered Oct 11 '18 at 23:11

Gordon Linoff

1,242,037
58
646
786

score 0 · Accepted Answer · answered Oct 11 '18 at 23:22

0

create table3 then execute the following

INSERT INTO table3 (name)
SELECT DISTINCT t2.name
FROM table2 t2 
LEFT JOIN table1 t1 on t2.product = t1.product
WHERE t1.product IS NOT NULL

answered Oct 11 '18 at 23:22

Prince Trotman

59
5

SQL filter entries which match all entries from another table

3 Answers3