SQL取得条件について

Question

ORCLEのSQLで質問です

2つのテーブルがあります
商品情報テーブルITEM
・ID/ID
・商品名/NAME

注文情報テーブルORDER
・ID/ID
・注文番号/ORDER_NO

注文情報明細テーブルORDER_DETAIL
・ID/ID
・注文番号/ORDER_NO
・明細番号/DETAIL_NO
・商品ID/ITEM_ID
・削除フラグ/DEL_FLG

購入されたことがある商品で、
全ての注文情報を見た時に、
注文明細情報テーブルにある商品IDの中で
明細が削除されて、他に削除されていない商品ID
を取得したいです

イメージは

↓商品情報 ITEM

ID	NAME
1	AAA
2	BBB
3	CCC

↓注文情報 ORDER

ID	ORDER_NO
1	001
2	002
3	003

↓注文明細情報 ORDER_DETAIL

ID	ORDER_NO	DETAIL_NO	ITEM_ID	DEL_FLG
1	001	01	AAA	0
2	001	02	BBB	0
3	001	03	CCC	1
4	002	01	AAA	1
5	002	02	BBB	0
6	002	03	CCC	1
7	003	01	AAA	0
8	003	02	BBB	0
9	003	03	CCC	1

この表でいうと
ITEM.NAMEの
AAAは、ORDER_NO=001で削除されていますが
002、003で削除されていないので、対象外
BBBは明細で削除されているレコードが無いので、対象外
CCCは、どの注文番号でも削除されているので、対象

このような感じです

一応ですが、WITH句を使わない案でお願いします

Comments

[Orlofsky]

ここの掲示板は丸投げ禁止ですから、今書いた SELECT文も質問に追記してください。

SQLで使われるテーブルはCREATE TABLEされている必要があります。質問のテーブル定義の説明もCREATE TABLEで、SELECTされるデータもINSERTされている必要がありますから、INSERT文で載せておくと適切なコメントが付き易いです。

https://teratail.com/help/question-tips#questionTips3-7 の [コード] を使って見易く載せてください。

[m.ts10806]

>一応ですが、WITH句を使わない案でお願いします

WITH問い合わせを適切な使い方を知ってるならこれくらいできると思いますが、何をどのように試してつまずいたのでしょうか。
それとも作業依頼でしょうか。

[Orlofsky]

宿題の丸投げの匂い。
新宿御苑が新型コロナウイルス感染を避けるため無料になるかもしれないから、行ってこよう。
http://shimajiro-mobiler.net/2020/03/21/post66143

Answer 1

select * from ITEM
where not exists(
       select 1 from ORDER_DETAIL where item_id=item.id and del_flg=0
      )
  and exists (
       select 1 from ORDER_DETAIL where item_id=item.id
      )

※ITEMに存在するものが必ずORDER_DETAILにも存在するなら、後半のexistsによる条件は不要です。

Answer 2

order_detailの中に存在する商品でdel_flgが全て削除のもの

select name from
(select distinct name, del_flg from order_detail) t1 
group by t1.name
having (count(name)=1 and max(del_flg)=1)