既に解決済みではありますが、、
フィールドA、フィールドB に複合ユニークインデックスが張ってあるなら、akio221様の回答と同様、
sql
1SELECT field_a FROM test WHERE field_b IN (1, 3, 4) GROUP BY field_a HAVING COUNT(*) = 3;
もし、フィールドA、フィールドB の値が重複するレコードがあり得るなら、
sql
1SELECT tmp.field_a FROM (
2 SELECT DISTINCT field_a, field_b FROM test WHERE field_b IN (1, 3, 4)
3) AS tmp GROUP BY tmp.field_a HAVING COUNT(*) = 3;
もちろん、HAVING COUNT(*) = 3
部分の'3'は
WHERE field_b IN (...)
の'...'部分に指定する値の個数と一致させる必要があります。
sql
1mysql> CREATE TABLE test (
2 -> field_a int,
3 -> field_b int
4 -> );
5Query OK, 0 rows affected (0.05 sec)
6
7mysql> INSERT INTO test VALUES
8 -> (1, 1),
9 -> (1, 3),
10 -> (1, 5),
11 -> (2, 2),
12 -> (2, 4),
13 -> (3, 1),
14 -> (3, 3),
15 -> (3, 4);
16Query OK, 8 rows affected (0.00 sec)
17Records: 8 Duplicates: 0 Warnings: 0
18
19mysql> SELECT * FROM test;
20+---------+---------+
21| field_a | field_b |
22+---------+---------+
23| 1 | 1 |
24| 1 | 3 |
25| 1 | 5 |
26| 2 | 2 |
27| 2 | 4 |
28| 3 | 1 |
29| 3 | 3 |
30| 3 | 4 |
31+---------+---------+
328 rows in set (0.00 sec)
33
34mysql> SELECT field_a FROM test WHERE field_b IN (1, 3, 4) GROUP BY field_a HAVING COUNT(*) = 3;
35+---------+
36| field_a |
37+---------+
38| 3 |
39+---------+
401 row in set (0.00 sec)
41
42mysql> INSERT INTO test VALUES (1, 3), (1, 3);
43Query OK, 2 rows affected (0.00 sec)
44Records: 2 Duplicates: 0 Warnings: 0
45
46mysql> SELECT * FROM test ORDER BY field_a, field_b;
47+---------+---------+
48| field_a | field_b |
49+---------+---------+
50| 1 | 1 |
51| 1 | 3 |
52| 1 | 3 |
53| 1 | 3 |
54| 1 | 5 |
55| 2 | 2 |
56| 2 | 4 |
57| 3 | 1 |
58| 3 | 3 |
59| 3 | 4 |
60+---------+---------+
6110 rows in set (0.00 sec)
62
63mysql> SELECT tmp.field_a FROM (
64 -> SELECT DISTINCT field_a, field_b FROM test WHERE field_b IN (1, 3, 4)
65 -> ) AS tmp GROUP BY tmp.field_a HAVING COUNT(*) = 3;
66+---------+
67| field_a |
68+---------+
69| 3 |
70+---------+
711 row in set (0.00 sec)