日付の前後関係で2つのテーブルを結合したい

質問

はじめまして。

親子関係を持つ２つのテーブルを結合するSQLについて教えてください。
データベースはAccess2003です。

親テーブル：契約基本
【基本SEQ】【契約番号】 【契約者名】【基本契約年月日】
1　　　　　K0001　　　　契約者１Ａ　2013/05/01
2　　　　　K0001　　　　契約者１Ｂ　2013/05/10
3　　　　　K0001　　　　契約者１Ｃ　2013/05/20
4　　　　　K0002　　　　契約者２Ａ　2013/06/10
5　　　　　K0002　　　　契約者２Ｂ　2013/06/10
6　　　　　K0002　　　　契約者２Ｃ　2013/06/10
7　　　　　K0003　　　　契約者３Ａ　2013/07/01
8　　　　　K0003　　　　契約者３Ｂ　2013/07/10
9　　　　　K0003　　　　契約者３Ｃ　2013/07/10

子テーブル：契約明細
【契約番号】【契約種別】【明細契約年月日】
K0001　　　　S1　　　　　2013/05/05
K0001　　　　S2　　　　　2013/05/10
K0002　　　　S1　　　　　2013/06/05
K0002　　　　S2　　　　　2013/06/10
K0003　　　　S1　　　　　2013/07/05
K0003　　　　S2　　　　　2013/07/15

契約基本テーブルの基本SEQはオートナンバーです。
この２つのテーブルを以下の条件で結合したいです。

・契約明細テーブルの１レコードについて、契約基本テーブルから１レコードずつ結合
・結合する契約基本テーブルの条件は、以下の通り
　１．契約明細・契約番号単位で、明細契約年月日に最も近い基本契約年月日のレコード
　　　（明細契約年月日を超えない。明細契約年月日と同一日はOK）
　２．１．の条件を満たす契約基本テーブルのレコードが複数存在する場合、
　　　その中で基本SEQが最大のレコード
　３．１．の条件を満たす契約基本テーブルのレコードが存在しない場合、
　　　該当する契約明細レコードは出力対象外。

以上の条件をもとに、１つのSQLで以下のような結果を取得したいです。

【契約番号】【契約種別】【明細契約年月日】【基本SEQ】【契約者名】【基本契約年月日】
K0001　　　　S1　　　　　2013/05/05　　　　1　　　　　契約者１Ａ　2013/05/01
K0001　　　　S2　　　　　2013/05/10　　　　2　　　　　契約者１Ｂ　2013/05/10
K0002　　　　S1　　　　　2013/06/05…[条件を満たす親レコードがないため出力対象外]
K0002　　　　S2　　　　　2013/06/10　　　　6　　　　　契約者２Ｃ　2013/06/01
K0003　　　　S1　　　　　2013/07/05　　　　7　　　　　契約者３Ａ　2013/07/01
K0003　　　　S2　　　　　2013/07/15　　　　9　　　　　契約者３Ｃ　2013/07/10

１つのSQLで処理したい理由は、そのSQLをクエリに登録して、データシートに紐付け、
一覧表示したいと考えているためです。

以上よろしくお願いします

回答No.4

30246kiku · ベストアンサー

＃２です

では、以下でどうなりますか

SELECT Q1.契約番号, Q1.契約種別, Q1.明細契約年月日, Q2.基本SEQ, Q2.契約者名, Q2.基本契約年月日
FROM 契約明細 AS Q1, 契約基本 AS Q2
WHERE Q2.基本SEQ = (SELECT TOP 1 基本SEQ FROM 契約基本 WHERE 契約番号=Q1.契約番号 AND 基本契約年月日<=Q1.明細契約年月日 ORDER BY 基本契約年月日 DESC, 基本SEQ DESC);

お礼

再度ご回答いただき、ありがとうございます。

期待通りの結果を得ることができました！！！

副問い合わせに関する理解が不足しているため、
上記のSQL文を理解するのに少し時間がかかりましたが、
いろいろ調べて、
なるほど！
と目から鱗でした。

大変助かりました。

回答No.5

world99

k28484　さん

横から余計な口出しをして申し訳ありませんが、テーブル設計が正しく行われていないように思います。

そのため契約明細と契約基本のデータを結合して取得するだけなのに、とても複雑な結合条件が必要でＳＱＬ文もかなり複雑になってしまっています。

一般的に親テーブルの主キーで子テーブルのデータを検索できなければなりません。その逆もしかり。

親テーブル「契約基本」の主キーは基本ＳＥＱですが、子テーブル「契約明細」にはその基本ＳＥＱが含まれていません。

基本契約年月日と明細契約年月日の近いという条件でテーブルを結合するとデータの不整合が起こります。

例えば２人の契約者が違うが同じ契約明細年月日の場合、基本ＳＥＱの値が大きい契約基本テーブルのデータを探すと、１人の契約者名しか表示されません。

１人の契約者は１つの契約種別のデータを持たないなら、テーブルを親子に分ける必要がありません。
（契約番号を主キーに持つテーブルがあると見受けられます。契約基本テーブルはそのテーブルの子テーブルなのではないでしょうか？）

■１つのテーブルにした例
契約テーブル　　主キー：基本ＳＥＱ
【基本ＳＥＱ】【契約番号】【契約者名】【基本契約年月日】【契約種別】【明細契約年月日】
　1　　　　　　　K0001　　　契約者１Ａ　2013/05/01　　　　S1　　　　　2013/05/05
　2　　　　　　　K0001　　　契約者１Ｂ　2013/05/10　　　　S1　　　　　2013/05/10
　3　　　　　　　K0001　　　契約者１Ｃ　2013/05/20　　　　S2　　　　　2013/05/25
　4　　　　　　　K0002　　　契約者２Ａ　2013/06/10　　　　S2　　　　　2013/06/10
　5　　　　　　　K0002　　　契約者２Ｂ　2013/06/10　　　　S1　　　　　2013/06/15
　6　　　　　　　K0002　　　契約者２Ｃ　2013/06/10　　　　S2　　　　　2013/06/10
　7　　　　　　　K0003　　　契約者３Ａ　2013/07/01　　　　S1　　　　　2013/07/05
　8　　　　　　　K0003　　　契約者３Ｂ　2013/07/10　　　　S2　　　　　2013/07/10
　9　　　　　　　K0003　　　契約者３Ｃ　2013/07/10　　　　S1　　　　　2013/07/10

１人の契約者が複数の契約種別のテータを持つ場合、テーブルは親子に分けます。親テーブルの主キーで子テーブルのデータを検索できるように子テーブルの主キーを決めます。

■親子にテーブルを分けた例
契約基本テーブル　　主キー：基本契約番号（基本ＳＥＱ）
【基本契約番号】【契約番号】【契約者名】【基本契約年月日】
　1　　　　　　　　K0001　　　契約者１Ａ　2013/05/01
　2　　　　　　　　K0001　　　契約者１Ｂ　2013/05/10
　3　　　　　　　　K0001　　　契約者１Ｃ　2013/05/20
　4　　　　　　　　K0002　　　契約者２Ａ　2013/06/10
　5　　　　　　　　K0002　　　契約者２Ｂ　2013/06/10
　6　　　　　　　　K0002　　　契約者２Ｃ　2013/06/10
　7　　　　　　　　K0003　　　契約者３Ａ　2013/07/01
　8　　　　　　　　K0003　　　契約者３Ｂ　2013/07/10
　9　　　　　　　　K0003　　　契約者３Ｃ　2013/07/10

契約明細テーブル　　主キー：基本契約番号（基本ＳＥＱ）、明細番号
【基本契約番号】【明細番号】【契約種別】【明細契約年月日】
　1　　　　　　　　1　　　　　S1　　　　　2013/05/05
　1　　　　　　　　2　　　　　S2　　　　　2013/05/10
　2　　　　　　　　1　　　　　S1　　　　　2013/05/05
　2　　　　　　　　2　　　　　S2　　　　　2013/05/10
　3　　　　　　　　1　　　　　S1　　　　　2013/05/05
　3　　　　　　　　2　　　　　S2　　　　　2013/05/10
　4　　　　　　　　1　　　　　S1　　　　　2013/06/05
　4　　　　　　　　2　　　　　S2　　　　　2013/06/10
　5　　　　　　　　1　　　　　S1　　　　　2013/06/05
　5　　　　　　　　2　　　　　S2　　　　　2013/06/10
　6　　　　　　　　1　　　　　S1　　　　　2013/06/05
　6　　　　　　　　2　　　　　S2　　　　　2013/06/10
　7　　　　　　　　1　　　　　S1　　　　　2013/07/05
　7　　　　　　　　2　　　　　S2　　　　　2013/07/15
　8　　　　　　　　1　　　　　S1　　　　　2013/07/05
　8　　　　　　　　2　　　　　S2　　　　　2013/07/15
　9　　　　　　　　1　　　　　S1　　　　　2013/07/05
　9　　　　　　　　2　　　　　S2　　　　　2013/07/15

お礼

ご指摘ありがとうございます。

余計な口出しではございません。
大変参考になりました。

親子テーブルの契約は、法人を対象としており、
契約者名は、契約担当者名だとお考え下さい。

なので例でいうと、K001は全て同じ法人となります。

今回やりたかったのは、契約明細が締結された時点で、
その親の契約基本の内容はどうだったのか、という一覧を
得たかったのです。

いただいたご指摘は今後のテーブル設計の参考にさせて
いただきます。

ありがとうございました。

回答No.3

root139

#1 です。
とりあえず、Access でも動く事は動いたようですね。

> 契約年月日の比較を優先し、１レコードに絞り込めなかった場合に基本SEQで比較したいのです。

なるほど。では、基本契約年月日が同じで基本SEQの大きなものが無いという条件に書き換えてみてはどうでしょうか。
変更したのは EXISTS() の中の WHERE 句です。

SELECT *
FROM 契約明細 km
INNER JOIN 契約基本 kk ON(km.契約番号 = kk.契約番号 AND kk.基本契約年月日 <= km.明細契約年月日)
WHERE
NOT EXISTS(SELECT * FROM 契約基本 kk2 WHERE kk2.契約番号 = km.契約番号 AND kk2.基本契約年月日 <= km.明細契約年月日 AND (kk.基本契約年月日 < kk2.基本契約年月日 OR kk.基本契約年月日 = kk2.基本契約年月日 AND kk.基本SEQ < kk2.基本SEQ))
ORDER BY 明細契約年月日;

お礼

再度ご回答いただき、ありがとうございます。

期待通りの結果を得ることができました。

EXISTS句はこれまで使ったことがなかったので
一見かなり難解なSQL文に見えたのですが、
こういう使い方があるんだな、
と大変勉強になりました。

今回いただいたご回答を参考に今後はEXSITS句も
積極的に使えるようになりたいと思います。

どうもありがとうございました。

回答No.2

30246kiku

以下でどうなりますか

SELECT Q1.契約番号, Q1.契約種別, Q1.明細契約年月日, Q2.基本SEQ, Q2.契約者名, Q2.基本契約年月日
FROM 契約明細 AS Q1, 契約基本 AS Q2
WHERE Q2.基本SEQ = (SELECT Max(基本SEQ) FROM 契約基本 WHERE 契約番号=Q1.契約番号 AND 基本契約年月日<=Q1.明細契約年月日);

※ 処理性能はわかりません

補足

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

root139さんのご回答にも補足させていただいたのですが、
基本契約年月日の前後関係が逆転することがあります。

基本SEQのMAXをとってしまうと
契約明細・契約番号単位で、明細契約年月日に最も近い
という条件を満たせない場合があるのです。

私の挙げた例がわかりにくくて申しわけありません。

基本契約年月日の並び順がバラバラという前提で
何か良いアイデアがございましたらご教示ください。

よろしくお願いします。

回答No.1

root139

手元に Access が無いので動作未確認ですが、下記の SQL ではどうですか？

SELECT *
FROM 契約明細 km
INNER JOIN 契約基本 kk ON(km.契約番号 = kk.契約番号 AND kk.基本契約年月日 <= km.明細契約年月日)
WHERE
NOT EXISTS(SELECT * FROM 契約基本 kk2 WHERE kk2.契約番号 = km.契約番号 AND kk2.基本契約年月日 <= km.明細契約年月日 AND (kk.基本契約年月日 < kk2.基本契約年月日 OR kk.基本SEQ < kk2.基本SEQ))
ORDER BY 明細契約年月日;

参考URL：

http://codezine.jp/article/detail/460

補足

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

大変勉強になりました。

私の挙げた具体例が悪く、申しわけありません。
基本契約年月日は更新可能で、前後関係が逆転する場合があります。

質問の例でK001だけに限定しますが、下記のようなパターンがありえます。
契約明細は質問の例のままとします。

親テーブル：契約基本
【基本SEQ】【契約番号】 【契約者名】【基本契約年月日】
1　　　　　K0001　　　　契約者１Ａ　2013/05/10
2　　　　　K0001　　　　契約者１Ｂ　2013/05/01
3　　　　　K0001　　　　契約者１Ｃ　2013/04/30

このような場合は、テーブル結合で下記の結果を得たいです。

【契約番号】【契約種別】【明細契約年月日】【基本SEQ】【契約者名】【基本契約年月日】
K0001　　　　S1　　　　　2013/05/05　　　　2　　　　　契約者１Ｂ　2013/05/01
K0001　　　　S2　　　　　2013/05/10　　　　1　　　　　契約者１Ａ　2013/05/10

契約年月日の比較を優先し、１レコードに絞り込めなかった場合に基本SEQで
比較したいのです。

再度お知恵をかしていただけませんでしょうか？