SQLで○○の値以外を持っているレコードを抽出しないという条件を作りた

Question

SQLで○○の値以外を持っているレコードを抽出しないという条件を作りたいのですが・・・
例えばこんな感じのテーブルがあったとします。

A1 A2 A3
-- -- --
AAA AA 111
AAA AB 111
AAA BB 222
BBB BB 111
CCC AA 111
CCC AC 555
DDD BB 222
EEE AA 111
EEE AC 333
EEE BB 777

A2の列に「BB」の値"しか"持っていないレコードを抽出したいのです

このテーブルで言えば返りは
BBB BB 111
DDD BB 222
と期待しています。

値BBのみしか持ってない条件の作り方を御教示願えませんでしょうか。
また、A2の値も文字数以外は規則性があるわけではないので無限パターンです。
詳しい方よろしくお願いします。

m-take0220 · Accepted Answer

＞ A1 A2 A3

＞ -- -- --
＞ AAA BB 222
＞ EEE BB 777
＞
＞が、どうして排除されるのか？

A2がBBでないレコードは

A1 A2 A3
-- -- --
AAA AA 111
AAA AB 111
CCC AA 111
CCC AC 555
EEE AA 111
EEE AC 333

ですよね。このレコードのA1のデータ(AAA,CCC,EEE)は排除したいという条件ですから、A1がAAAとEEEである

A1 A2 A3
-- -- --
AAA BB 222
EEE BB 777

は排除されます。

サブクエリでは、

select A1 from テーブル名 where A2<>'BB'

として、A2がBBでないレコードのA1のデータを取得しています。
条件がA1 not inなので、一方の条件がA2='BB'でもう一方がA2<>'BB'であっても問題はないですよね。

テーブル名を変えないとだめかもしれなかったので、「間違ってるかも」と書いたのですが、

select * from テーブル名 tablea where tablea.A2='BB' and tablea.A1 not in (select tableb.A1 from テーブル名 tableb where tableb.A2<>'BB')

ならわかりますか。(すいません。今回も実行はしていません。)

yamada_g · Answer

No.8です。

> ○select * from テーブル名 where A2='BB' and not exists (select A1 from テーブル名 where A2<>'BB')
違います。not exists で書くならこうです。
select * from テーブル名 t1 where t1.A2='BB' and not exists (select * from テーブル名 t2 where t1.A1 = t2.A1 and t2.A2<>'BB')

>なぜか（方言のせい？）
なので、使用しているDB名やバージョンは明記してください。
こちらには何もわかりません。

yambejp · Answer

大前提としてA1とA2の組合せがユニークかどうか？でだいぶ違います。

もし上の条件を適用してよいなら
SELECT MAIN.A1,A2,A3 FROM テーブル AS MAIN
INNER JOIN (SELECT A1 FROM hoge GROUP BY A1 HAVING COUNT(*)=1) AS SUB 
ON SUB.A1=MAIN.A1
WHERE MAIN.A2='BB'

みたいなのでもいけそうです。

t_nojiri · Answer

ああ、皆さんの説明で大体やっと判りました。
A1列で重複した物の無い、A2列はBBの条件の行をSELECTしたかったのですね。
解説は皆さん書いてるので省略します。

A1列について条件何も書いてないから私の拙い文書解読能力じゃ紐解くのに時間かかってしまいました。

yamada_g · Answer

横からすみません。
>A1 A2 A3
>-- -- --
>AAA BB 222
>EEE BB 777
>
>が、どうして排除されるのか？
ですが、
>A2の列に「BB」以外の値を持ったA1列データは排除して
という条件から、
AAAは
>AAA AA 111
>AAA AB 111
同じくEEEは
>EEE AA 111
>EEE AC 333
というA2が「BB」以外の値を持ったレコードが存在しているため排除する、ということではないのですか？

SQLも
>select * from テーブル名 where A2='BB' and A1 not in (select A1 from テーブル名 where A2<>'BB')
で問題はないと思います。
not existsの方が良さそうな気はしますが。

layy · Answer

A1とA2列をグループ化し集約すると
AAAは３種類
BBBは１種類
CCCは２種類
DDDは１種類
EEEは３種類
で、
２種類以上あれば他にもあるということ

そのうちBBを持つのは
BBB
DDD

仮に
BBB BB
BBB BB
と２行あってもカウントすれば判定は可能

SELECT文の中にSELECT文では？。

t_nojiri · Answer

つーか、ここで真面目に回答してるのかどうか分からないけど

>select * from テーブル名 where A2='BB' and A1 not in (select A1 from テーブル名 where A2<>'BB')

で、A2='BB' と A2<>'BB'が正反対の条件かつ絶対成立しないの分からないんだったら、池沼じゃないのか？

t_nojiri · Answer

m-take0220様後学のために教えて下さい。

＞ A2の列に「BB」以外の値を持ったA1列データは排除して

A1 A2 A3
-- -- --
AAA BB 222
EEE BB 777

が、どうして排除されるのか？
私の拙い知識では排除される対象になり得るように読めないのですが。

bonaron · Answer

SELECT T1.A1, T1.A2, T1.A3
FROM テーブル AS T1 LEFT JOIN 
(SELECT T21.A1, T21.A2
 FROM テーブル AS T21 
 INNER JOIN テーブル AS T22 
 ON T21.A1 = T22.A1
 WHERE T21.A2="BB" AND T22.A2<>"BB"
 GROUP BY T21.A1, T21.A2)  AS T2 
ON T1.A2 = T2.A2 AND T1.A1 = T2.A1
WHERE T1.A2="BB" AND T2.A2 Is Null

サブクエリの部分は方言がありますから
動作しない環境もあります。
環境に合わせて修正してください。

Access 2007 で動作確認しています。

Access 2003 なら サブクエリ部分は
[SELECT ・・・].AS T2
だったかな？

なお、仕様がはっきりしなかったので、

FFF BB 111
　FFF BB 222
　GGG BB 333
　GGG BB 333

このようなデータは、すべてのレコードが抽出されます。

※これを抽出しない場合は、かなり難しいかも。私は降ります。

m-take0220 · Answer

＞ だから、何でAAAとEEEが対象にならないのか上記説明で表現出来ていますか？

できてますよね。だって、

＞ A2の列に「BB」以外の値を持ったA1列データは排除して

という条件が加わったのですから。

select * from テーブル名 where A2='BB' and A1 not in (select A1 from テーブル名 where A2<>'BB')

でどうでしょうか。実行していないので間違ってるかも。
考え方は、排除したいA1のデータをサブクエリで抽出して、A1がそのリストに含まれていないという条件をつけます。

SQLで○○の値以外を持っているレコードを抽出しないという条件を作りた

＞ A1 A2 A3

No.8です。

大前提としてA1とA2の組合せがユニークかどうか？でだいぶ違います。

ああ、皆さんの説明で大体やっと判りました。

横からすみません。

A1とA2列をグループ化し集約すると

つーか、ここで真面目に回答してるのかどうか分からないけど

m-take0220様後学のために教えて下さい。

SELECT T1.A1, T1.A2, T1.A3

＞ だから、何でAAAとEEEが対象にならないのか上記説明で表現出来ていますか？

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

＞だから、何でAAAとEEEが対象にならないのか上記説明で表現出来ていますか？