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

質問

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の値も文字数以外は規則性があるわけではないので無限パターンです。
詳しい方よろしくお願いします。

回答No.9

m-take0220 · ベストアンサー

＞ 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')

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

お礼

ありがとうございます　参考にいたします

回答No.12

yamada_g

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名やバージョンは明記してください。
こちらには何もわかりません。

お礼

ありがとうございます
自力で何とかなりました

回答No.11

yambejp

大前提として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'

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

お礼

A1＋A2は完全にユニークです。
参考にさせていただきます
ありがとうございます

回答No.10

t_nojiri

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

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

お礼

申し訳ありませんでした

回答No.8

yamada_g

横からすみません。
>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の方が良さそうな気はしますが。

お礼

解説までしていただきありがとうございます
なぜか（方言のせい？）not inをnot existsに変えると構文エラーで動いてくれなくなりました。
○select * from テーブル名 where A2='BB' and A1 not in (select A1 from テーブル名 where A2<>'BB')
　　　　　　　　　　　　　　　　　　　　　↓
×select * from テーブル名 where A2='BB' and A1 not exists (select A1 from テーブル名 where A2<>'BB')

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

回答No.7

layy

A1とA2列をグループ化し集約すると
AAAは３種類
BBBは１種類
CCCは２種類
DDDは１種類
EEEは３種類
で、
２種類以上あれば他にもあるということ

そのうちBBを持つのは
BBB
DDD

仮に
BBB BB
BBB BB
と２行あってもカウントすれば判定は可能

SELECT文の中にSELECT文では？。

お礼

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

回答No.6

t_nojiri

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

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

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

お礼

とりあえずお礼だけさせていただきます

回答No.5

t_nojiri

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

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

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

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

お礼

すみません

回答No.4

bonaron

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

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

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

お礼

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

回答No.3

m-take0220

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

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

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

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

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

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

お礼

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

回答No.2

t_nojiri

>A2の列に「BB」以外の値を持ったA1列データは排除して「BB」の値"しか"持っていないレコードを抽出

だから、何でAAAとEEEが対象にならないのか上記説明で表現出来ていますか？
論理的に正しく説明できない限りプログラムは組む事が出来ません。

まあ、無理やりっていうのなら

select * from テーブル名 where A1 BETWEEN 'BBB' AND 'DDD'
AND A2 = 'BB';

みたいな構文にはなるんでしょうけど。(適当に書いてるので検証はしてませんけど)

お礼

うまく通じずすみません