PostgreSQLの検索クエリについて～のみ含んだデータを取得する方法が知りたい

Question

Postgresのクエリーについて。

以下のようなデータがあります。

id	user_id	code
1	10	aaa1
2	10	bbb1
3	11	aaa2
4	11	bbb2
5	12	aaa3
6	12	bbb3
7	12	ccc3
8	13	aaa4
9	14	aaa5
10	14	zzz1

codeに対し、"aaa"と"zzz"のみ含んだuser_idを取得したい場合、
どのような検索クエリとなるでしょうか。

理想としては、上の表からuser_id:14が取得したいといった意図となります。

また、ネットで調べたりして試したクエリーは以下の通りです。

SELECT
    a.user_id
FROM
    table AS a
WHERE
    (a.code LIKE '%aaa%' OR a.code LIKE '%zzz%')
    AND NOT EXISTS(
        SELECT
            *
        FROM
            table as b
        WHERE
            b.id = a.id
            AND b.code NOT LIKE '%aaa%'
            AND b.code NOT LIKE '%zzz%'
    )
GROUP BY
    a.user_id

よろしくお願いします。

Comments

[maisumakun]

試しに書いてみたコードなどはありますか？

[m.ts10806]

テーブル定義とサンプルデータをSQLで提示してください。
また、自身が試したこと調べたことも記載してください。

[退会済みユーザー]

サブクエリーなしに出そうな気がするけど、どういう結果がほしいんだろうか？

[kazu5674]

返信が遅れてすみません、また初期段階で必要な情報が足りておらずすみません。
次回からの質問時に参考にさせて頂きます。

Answer 1

codeに対し、"aaa"と"zzz"のみ含んだuser_id

codeの内容に、"aaa"もしくは"zzz"を含んだものが、共に存在するuser_id

SELECT  user_id
from tbl a
where exists(
        select 1 from tbl where code LIKE '%aaa%' and user_id =a.user_id
      )
  and exists(
        select 1 from tbl where code LIKE '%zzz%' and user_id =a.user_id
      )
group by user_id

Comments

[kazu5674]

ありがとうございます！参考にさせて頂きました！

Answer 2

「"aaa"と"zzz"のみ含んだuser_id」の解釈が微妙ですが、「aaa を含む code と zzz を含む code の両方を持ち、それ以外の code (bbb や ccc など) を持たない user_id」ということなら、こんな感じでしょうか。

create table "table" (id int primary key, user_id int, code text);

insert into "table" values (1, 10, 'aaa1');
insert into "table" values (2, 10, 'bbb1');
insert into "table" values (3, 11, 'aaa2');
insert into "table" values (4, 11, 'bbb2');
insert into "table" values (5, 12, 'aaa3');
insert into "table" values (6, 12, 'bbb3');
insert into "table" values (7, 12, 'ccc3');
insert into "table" values (8, 13, 'aaa4');
insert into "table" values (9, 14, 'aaa5');
insert into "table" values (10, 14, 'zzz1');

insert into "table" values (11, 15, 'aaa6');
insert into "table" values (12, 15, 'bbb6');
insert into "table" values (13, 15, 'zzz6');

select distinct user_id
from "table"
where user_id in (select user_id from "table" where code like '%aaa%')
  and user_id in (select user_id from "table" where code like '%zzz%')
  and user_id not in (select user_id from "table" where code not like '%aaa%' and code not like '%zzz%');

なお、aaazzz があるとそれも引っかかりますね…。

insert into "table" values (14, 16, 'aaazzz');

---

追記。intersect と except でもいけますね。

(select user_id from "table" where code like '%aaa%'
 intersect
 select user_id from "table" where code like '%zzz%')
except
select user_id from "table" where code not like '%aaa%' and code not like '%zzz%';

Comments

[kazu5674]

ありがとうございます！参考にさせて頂き、無事解決できました！