SQLで複数行にマッチする結果を抽出したい

Question

環境

mariadb:10.4

わからないこと

3つの多対多のテーブルがあります。

SELECT * FROM address;

SELECT * FROM type;

SELECT * FROM address_type;

addressとtypeはどちらも増えていきます。

やりたいこと（抽出条件）

address.nameが'a1@d1','a4@d1','a5@d1','a6@d1'であること
かつ
address_type.type_idが1と3だけを持つもの。

テストデータですと、'a4@d1'だけを抽出したいです。

やったこと

SELECT * FROM address_type t left outer join address a on t.address_id = a.id
WHERE
	a.name IN ('a1@d1','a4@d1','a5@d1','a6@d1')
	-- AND (t.type_id = 1 AND t.type_id=3); -- これだとなにも出力されない

上記を考えたのですがダメでした。。

どうしたらいいのか。。。困っています。。

Comments

[sousuke]

address_type.type_idが1と3だけを持つもの。
に関して１だけ、３だけの場合は対象になるのですか？
例えばaddress_id:7,type_id:1の１行だけみたいなパターンは？

あ、1とかがそのパターンでしたね。1と3の両方が必要ということで理解しました。

Answer 1

まず、type側の条件を満たすクエリを作ります。
address_idごとにtype_idが1,3の行hastypeとtype_idが1,3以外の行hasnottype集計するクエリq1を作成します。
条件に合致するパターンは、hastype=2,hasnottype=0となるはずです。

次にaddress.name側の条件を満たすクエリを作ります。
その際、q1からhastype=2,hasnottype=0を満たすaddress_idが存在するかexistsで確かめればよいでしょう。

with q1 as (
	select DISTINCT
		address_id
		,sum(case when type_id in (1,3) then 1 else 0 end) over(partition by address_id) hastype
		,sum(case when type_id not in (1,3) then 1 else 0 end) over(partition by address_id) hasnottype		
	from address_type
)
select tbl1.name from address tbl1
where tbl1.name in ('a1@d1','a4@d1','a5@d1','a6@d1')
	and exists(
		select address_id 
		from q1
		where hastype = 2 and hasnottype = 0	
			and tbl1.id = q1.address_id
	)
;

case式でいろんなことができます。参考リンクをぜひ熟読してください。
CASE式のススメ

Comments

[n-o-n]

CASE式とっても便利ですね！勉強になります！
ありがとうございます！

Answer 2

1,3を1 以外を9に置き換え、加算した結果が２になるものを抽出

SELECT * 
FROM  address_type t left outer join address a on t.address_id = a.id
WHERE a.name IN ('a1@d1','a4@d1','a5@d1','a6@d1')
  AND t.address_id in (
        select address_id 
        from address_type 
        group by address_id
        having sum(case when type_id in (1,3) then 1 else 9 end)=2
      )

Comments

[sousuke]

これだとa6も入ると思います。

[sazi]

ですね。
訂正しました。

[n-o-n]

こんなやり方があったんですね！！助かりました！
ありがとうございます！！

Answer 3

高速回答ありがとうございます！

実際にいただいた２つのSQL動作を確認させていただいたのですが、
'a6@d1'もひっかかってしまいます。。