SQLの作成を手伝っていただけませんか

質問

会社の組織構造を表す「組織テーブル」とそこに所属する社員を表す「社員テーブル」から、データを抽出したいのですが、わたしが設定しようとした条件とはまったく違う結果が返ってきてしまいます。あつかましいお願いですが、お手伝いいただけたら幸いです。

テーブルは以下のような構造です（*がプライマリーキイ）。
1. 組織テーブル ORGANISATIONS
ORG__KEY*, ORG_NAME, ORG_TYPE
一つのレコードにプライマリーキイのほかに組織名と組織タイプが入っています。

2. 社員テーブル EMPLOYEES
ORG__KEY*, EMP_NO*, EMP_NAME, SEXUALITY
組織のプライマリーキイとその組織に着任した順序を表すEMP_NOでレコードを特定します。名前と性別(MかFを入力)も登録されています。

抽出条件は常設タイプ(ORGANISATIONS.ORG_TYPE = 'PERM')の組織のうち、所属する社員が全て男性であるものは何かです。また、組織名に「事業部」という文字列を含まないことも条件です。

わたしの書いた以下のSQLでは社員のいない組織、つまり「組織テーブル」に登録されているだけで「社員テーブル」にレコードを持たない組織のIDが返ってきます。

select ORG__KEY from ORGANISATIONS a
where ORG_TYPE = 'PERM'
and not ORG_NAME like '%事業部%'
and 'M' = all
(select SEXUALITY from EMPLOYEES b
where a.ORG__KEY = b.ORG__KEY)

男性社員だけの組織が存在しなければしないで、レコードを返さないようにSQLを書かなければならないのですが、どのように改善すればよいでしょうか。組織タイプや組織名の条件を外して検索すると、社員なし組織だけでなく、実際に男性社員だけの組織も取得できるので、まったく違うことをやっているわけではないとおもいますが、何かが足りないのでしょうか。
まだまだ初心者です。説明がこんがらかっているかもしれませんが、よろしくお願いします。

回答No.4

MZ-80B · ベストアンサー

仕様通りに書くだけが道ではないです。
視点を変えれば、違った書き方でもいけるでしょう
最初の SELECT と組織の積集合は結合に変換しても
いいですが、遅くなりそうな雰囲気がします。

SELECT ORG_KEY
FROM EMPLOYEES
WHERE SEXUALITY = 'M'
--
MINUS
SELECT ORG_KEY
FROM EMPLOYEES
WHERE SEXUALITY = 'F'
--
INTERSECT
SELECT ORG_KEY
FROM ORGANISATIONS
WHERE
ORG_TYPE = 'PERM'
AND
ORG_NAME NOT LIKE '%事業部%'

内容の検証はお任せします。

お礼

明快なご回答ありがとうございます。

回答No.3

k_o_r_o_c_h_a_n

select ORG__KEY from ORGANISATIONS a
where
ORG_TYPE = 'PERM' and
not (ORG_NAME like '%事業部%') and
not exists (select * from EMPLOYEES b where a.ORG__KEY=b.ORG__KEY and SEXUALITY<>'M')

and
exists (select * from EMPLOYEES b where a.ORG__KEY=b.ORG__KEY and SEXUALITY='M')

を追加することで、確実に♂が居て、♀が居ない部署に
限定されますよね。

効率的には、サブクエリを増やすのは面白くないですが、
判りやすい判断条件かと思います。

お礼

補足の条件判断までお付き合いくださりありがとうございました。

回答No.2

7colors

Oracle10Gとかだったら、REGEXP_LIKEとかでしきいちを設定して抽出でよいのかも。
NOT ～ LIKE '%%'は検索かなり遅いはずなので、DBに事業部を含む場合にFLGを立てるようにしてから抽出のほうが、効率的な検索ができるようになると思うけど・・・。

回答No.1

k_o_r_o_c_h_a_n

select ORG__KEY from ORGANISATIONS a
where
ORG_TYPE = 'PERM' and
not (ORG_NAME like '%事業部%') and
not exists (select * from EMPLOYEES b where a.ORG__KEY=b.ORG__KEY and SEXUALITY<>'M')

で良いんじゃないかな。
”♂以外が居ないこと”を検証すれば良いかと。
（誰も所属しない幽霊部署がない前提です）

補足

k_o_r_o_c_h_a_nさんありがとうございます。検索結果から幽霊部署を除外する為にはどうしたらよいでしょうか。