QA@IT

SQLで履歴を含まないデータの抽出ができそうでできません

4729 PV

SQLで履歴を含まないデータの抽出ができそうでできません。
半分くらいはできますが、どうしても最後まで抽出できません。
分かる方は、ぜひ助けてください!!

【元データ】
・マスタ
No  名称
1  マスタ1
2  マスタ2
3  マスタ3

・社員マスタ
社員番号 氏名
001   氏名1
002   氏名2

・履歴データ
No  社員番号
1  001
2  001

【抽出したいデータ】
No  社員番号
3  001
1  002
2  002
3  002

【半分くらいできたSQL】
select マスタ.No,T1.社員番号 from マスタ
not exists(select 'X' from 履歴データ where 履歴データ.No=マスタ.No and 履歴データ.社員番号=
(select 社員番号 from 社員マスタ)) AS T1

【失敗した抽出】
No  社員番号
3  001

どうして社員番号が002のデータが出ないのでしょうか?
Accessで作業しているのでAccessで出せる程度のSQLが助かります。
もしAccessで無理でも、今後のためにSQLで出す方法を知りたいです。

  • 各テーブルの関連性、抽出データがなぜそのようなデータになるのかが分からないので的確な回答は得られないと思います。 -
  • その【半分くらいできたSQL】っておかしくない?
    existsってFROM句で使えたっけ?
    Accessが特殊なのかと2010でやってみたら、「FROM句の構文エラーです。」って言われた。
    -

回答

以下のSQL文でよいかと思います。
社員マスタとマスタをクロス結合(Accessでは,で区切るだけ)して履歴データをLeft Join
そしてWhere句により履歴テーブルのレコードがないことを No is Null により判断します。

SELECT A.[No], A.社員番号, A.氏名, A.名称
FROM (SELECT [マスタ].[No], 社員マスタ.社員番号, 社員マスタ.氏名, [マスタ].名称
FROM 社員マスタ, マスタ)  AS A LEFT JOIN 履歴データ AS B ON (A.[No] = B.[No]) AND (A.社員番号 = B.社員番号)
WHERE (((B.[No]) Is Null))
ORDER BY A.社員番号, A.[No]
編集 履歴 (0)
  • このSQLでも抽出したいデータが取れました。
    ありがとうございました。
    -

not existsを使うならこれでどうでしょう

SELECT *
FROM (
  SELECT [マスタ].[No], 社員マスタ.社員番号
  FROM [マスタ], 社員マスタ) AS T1
  WHERE NOT EXISTS (
    SELECT *
    FROM 履歴データ AS T2
    WHERE T1.[No]=T2.[No] AND T1.社員番号=T2.社員番号)
ORDER BY 社員番号, [No];

マスタ.Noと社員マスタ.社員番号のすべての組み合わせ(T1)から、
履歴データに存在しない(not exists)レコードを抽出し、
社員番号,Noの順でソート

左外部結合(LEFT JOIN)とどっちが良いかは、
実行計画でも取って検討してみて下さい。
Accessでもレジストリにキーを追加すれば、実行計画を取得できます。

編集 履歴 (0)
  • このSQLでも抽出したいデータが取れました。
    ありがとうございました。
    -

h-satomiさんのいう通り間違っていました。すいません。
まだ半分もできていない感じです。。

【半分くらいできたSQL:訂正版】
select 履歴データ.No,履歴データ.社員番号
from マスタ left join 履歴データ on 履歴データ.No=マスタ.No
where not exists(select 'X' from 履歴データ where 履歴データ.No=マスタ.No and 履歴データ.社員番号='001'
※社員番号を'001'としていますが、社員番号を'002'としても'002'のデータは抽出できません。
 社員番号'002'のデータも抽出できて、そこに社員番号を渡せたらいいと思います。

Shuさんのいう通り、説明不足でしたので追記します。
【関連性】
(マスタ.No)⇔(履歴データ.No⇔履歴データ.社員番号)⇔(社員マスタ.社員番号)

【抽出したいデータの説明】
社員マスタの社員ごとに、マスタのデータの中で履歴データを保持していないデータを抽出したいです。
具体的には、マスタとは研修マスタで、研修履歴のない人に対して研修するデータを抽出したいと思っています。

よろしくお願いします。

編集 履歴 (0)
  • 質問の補足や修正は質問を直接編集するようにして、回答は回答だけに限定してください。 -
ウォッチ

この質問への回答やコメントをメールでお知らせします。