【MySQL】全て同じ値のカラムを持つレコードの取得

Question

タイトルが適切ではない気がするのですが以下の場合、全てdtb_products_class.del_flgが「1」のproduct_idを取得したいです。
product_id:100はdel_flgが「0」と「1」のふたつがあるので対象外で、product_id:200は「1」のみなので対象になります。

mysql> select p.product_id, pc.del_flg from dtb_products as p
    -> inner join dtb_products_class as pc
    -> on p.product_id = pc.product_id
    -> where p.del_flg = 0;
+------------+---------+
| product_id | del_flg |
+------------+---------+
|        100 |       1 |
|        100 |       0 |
|        100 |       0 |
|        100 |       0 |
|        100 |       1 |
|        200 |       1 |
|        200 |       1 |
|        200 |       1 |
|        200 |       1 |
|        200 |       1 |
+------------+---------+

色々調べたのですが、どこからどう書いていいのかわからなくなってしまったのでどなたか教えていただけないでしょうか。

MySQL 5.6.27です。
ECCUBE2.13で開発しています。

Answer 1

詳細なスキーマがわからないので、実行していませんが、
こんな感じ？

select 
	p.product_id
from 
	dtb_products as p
inner join 
	dtb_products_class as pc
on 
	p.product_id = pc.product_id
group by 
	p.product_id
having 
	count(distinct pc.del_flg) = 1

Comments

[tomoyuki123]

皆様ありがとうございました！

[miyahan]

>tomoyuki123 さん
このクエリだと、del_flg がすべて 0 の product_id もヒットするので気を付けてくださいね。

Answer 2

dtb_products_class.del_flgカラムがとり得る値は 0 と 1 のどちらかのみ、という前提であれば、

SELECT DISTINCT p.*
FROM dtb_products AS p
LEFT OUTER JOIN dtb_products_class AS pc_1 ON p.product_id = pc_1.product_id AND pc_1.del_flg = 1
LEFT OUTER JOIN dtb_products_class AS pc_0 ON p.product_id = pc_0.product_id AND pc_0.del_flg = 0
WHERE pc_1.del_flg IS NOT NULL
  AND pc_0.del_flg IS NULL;

もしくは

SELECT DISTINCT p.*
FROM dtb_products AS p
INNER JOIN dtb_products_class AS pc_1 ON p.product_id = pc_1.product_id
LEFT OUTER JOIN dtb_products_class AS pc_0 ON p.product_id = pc_0.product_id AND pc_0.del_flg = 0
WHERE pc_1.del_flg = 1
  AND pc_0.del_flg IS NULL;

実行結果

mysql> SELECT * FROM dtb_products;
+------------+
| product_id |
+------------+
|        100 |
|        200 |
|        300 |
|        400 |
+------------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM dtb_products_class;
+------------+---------+
| product_id | del_flg |
+------------+---------+
|        100 |       1 |
|        100 |       0 |
|        100 |       0 |
|        100 |       0 |
|        100 |       1 |
|        200 |       1 |
|        200 |       1 |
|        200 |       1 |
|        200 |       1 |
|        200 |       1 |
|        300 |       0 |
|        300 |       0 |
+------------+---------+
12 rows in set (0.00 sec)

mysql> SELECT DISTINCT p.*
    -> FROM dtb_products AS p
    -> LEFT OUTER JOIN dtb_products_class AS pc_1 ON p.product_id = pc_1.product_id AND pc_1.del_flg = 1
    -> LEFT OUTER JOIN dtb_products_class AS pc_0 ON p.product_id = pc_0.product_id AND pc_0.del_flg = 0
    -> WHERE pc_1.del_flg IS NOT NULL
    ->   AND pc_0.del_flg IS NULL;
+------------+
| product_id |
+------------+
|        200 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT DISTINCT p.*
    -> FROM dtb_products AS p
    -> INNER JOIN dtb_products_class AS pc_1 ON p.product_id = pc_1.product_id
    -> LEFT OUTER JOIN dtb_products_class AS pc_0 ON p.product_id = pc_0.product_id AND pc_0.del_flg = 0
    -> WHERE pc_1.del_flg = 1
    ->   AND pc_0.del_flg IS NULL;
+------------+
| product_id |
+------------+
|        200 |
+------------+
1 row in set (0.00 sec)

Answer 3

記載されているSQLと実行結果が同じであれば、
他の方の回答は
dtb_products.del_flg = 0;
が抜けているんですかね。
（マスタで生きてて、クラス（？）で１件も存在しない商品を抽出したい？）

基本的にHAVINGで十分かと思いますが、
HAVINGやGROUP BYを使わずに
全て出力するなら、以下のSQLでも抽出できます。

SELECT	pc.product_id
	,	pc.del_flg
FROM	dtb_products_class pc
			INNER JOIN dtb_products p ON(
					p.product_id	= pc.product_id
				AND	p.del_flg		= 0
			)
WHERE	pc.del_flg		= 1
	AND	NOT EXISTS(
			SELECT	'X'
			FROM	dtb_products_class pc2
			WHERE	pc2.product_id	= p.product_id
				AND	pc2.del_flg		= 0
		)

Answer 4

こんな感じでしょうか？

select product_id
from 
(
    select 
        p.product_id as product_id,
        pc.del_flg as del_flg
    from 
        dtb_products as p
    inner join 
        dtb_products_class as pc
    on 
        p.product_id = pc.product_id
    group by 
        p.product_id
    having 
        count(distinct pc.del_flg) = 1
)as a
where del_flg = 1
;

Answer 5

※HAVING句を使った方法に書き直し

実現方法はいくつかありますが、たとえば del_flg がすべて 1 ということは、言い換えれば del_flg が 0 になっているレコードが１つもない product_id を検索すればよいので、

SELECT product_id FROM dtb_products
INNER JOIN dtb_products_class USING(product_id)
GROUP BY product_id
HAVING MIN(del_flg) = 1;

という感じで、各 product_id における del_flg の最低値が 1 (＝0が存在しない) という条件で抽出してあげればよいと思います。

Comments

[miyahan]

相関サブクエリなので遅いですね･･･。Kosuke_Shibuya さんの HAVING を使った方法のほうがシンプルかつ高速で良いと思います。

[miyahan]

パクりですが、サブクエリからHAVING句に書き直しました。