同一テーブル内で該当ステータスのレコードが存在しないものを抽出する方法

Question

MySQLにて、条件に一致するデータの抽出を行っておりますが、下記条件での組み方がわからず苦戦しております。
ご教示のほど、よろしくお願いいたします。

■登録データ例
テーブル名:status

id | status_cd

aa | 200
aa | 100
bb | 200
cc | 100

status_cd
100 契約中
200 廃止済み

一度廃止して再契約した場合、同じidで廃止と契約の2レコードが作成されます。(積み上げ)

■条件
status_cdが200だけのidを抽出したい。

求める結果としては、「bb」だけ抽出されるようにしたいです。
aaに関しては一度廃止し再契約しており、現在は100:契約中なので対象外です。

■試したこと
selet id
from status
where not exists (
select id
from status
where status_cd = 100)
;

■試したことの結果
レコードが取得できませんでした。

Comments

[Orlofsky]

>一度廃止して再契約した場合、同じidで廃止と契約の2レコードが作成されます。(積み上げ)

現在の情報と履歴情報をいっしょに持つことに執着して破綻したシステムを知っています。悲惨なのでわたしは見捨てました。

Answer 1

こんな感じですかね。

select a.id from status a where a.status_cd = 200 and not exists (select b.id from status b where a.id == b.id and b.status <> 200)

Comments

[nein]

ありがとうございます！
where句で200のものを指定する必要もあったんですね…
とても勉強になりました！

[tabuu]

Orlofskyさんもおっしゃっている通り良い設計とは思えませんので見直しをおすすめします。