はじめまして。
親子関係を持つ2つのテーブルを結合するSQLについて教えてください。
データベースはAccess2003です。
親テーブル:契約基本
【基本SEQ】【契約番号】 【契約者名】【基本契約年月日】
1 K0001 契約者1A 2013/05/01
2 K0001 契約者1B 2013/05/10
3 K0001 契約者1C 2013/05/20
4 K0002 契約者2A 2013/06/10
5 K0002 契約者2B 2013/06/10
6 K0002 契約者2C 2013/06/10
7 K0003 契約者3A 2013/07/01
8 K0003 契約者3B 2013/07/10
9 K0003 契約者3C 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はオートナンバーです。
この2つのテーブルを以下の条件で結合したいです。
・契約明細テーブルの1レコードについて、契約基本テーブルから1レコードずつ結合
・結合する契約基本テーブルの条件は、以下の通り
1.契約明細・契約番号単位で、明細契約年月日に最も近い基本契約年月日のレコード
(明細契約年月日を超えない。明細契約年月日と同一日はOK)
2.1.の条件を満たす契約基本テーブルのレコードが複数存在する場合、
その中で基本SEQが最大のレコード
3.1.の条件を満たす契約基本テーブルのレコードが存在しない場合、
該当する契約明細レコードは出力対象外。
以上の条件をもとに、1つのSQLで以下のような結果を取得したいです。
【契約番号】【契約種別】【明細契約年月日】【基本SEQ】【契約者名】【基本契約年月日】
K0001 S1 2013/05/05 1 契約者1A 2013/05/01
K0001 S2 2013/05/10 2 契約者1B 2013/05/10
K0002 S1 2013/06/05…[条件を満たす親レコードがないため出力対象外]
K0002 S2 2013/06/10 6 契約者2C 2013/06/01
K0003 S1 2013/07/05 7 契約者3A 2013/07/01
K0003 S2 2013/07/15 9 契約者3C 2013/07/10
1つのSQLで処理したい理由は、そのSQLをクエリに登録して、データシートに紐付け、
一覧表示したいと考えているためです。
以上よろしくお願いします
No.4ベストアンサー
- 回答日時:
#2です
では、以下でどうなりますか
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
- 回答日時:
k28484 さん
横から余計な口出しをして申し訳ありませんが、テーブル設計が正しく行われていないように思います。
そのため契約明細と契約基本のデータを結合して取得するだけなのに、とても複雑な結合条件が必要でSQL文もかなり複雑になってしまっています。
一般的に親テーブルの主キーで子テーブルのデータを検索できなければなりません。その逆もしかり。
親テーブル「契約基本」の主キーは基本SEQですが、子テーブル「契約明細」にはその基本SEQが含まれていません。
基本契約年月日と明細契約年月日の近いという条件でテーブルを結合するとデータの不整合が起こります。
例えば2人の契約者が違うが同じ契約明細年月日の場合、基本SEQの値が大きい契約基本テーブルのデータを探すと、1人の契約者名しか表示されません。
1人の契約者は1つの契約種別のデータを持たないなら、テーブルを親子に分ける必要がありません。
(契約番号を主キーに持つテーブルがあると見受けられます。契約基本テーブルはそのテーブルの子テーブルなのではないでしょうか?)
■1つのテーブルにした例
契約テーブル 主キー:基本SEQ
【基本SEQ】【契約番号】【契約者名】【基本契約年月日】【契約種別】【明細契約年月日】
1 K0001 契約者1A 2013/05/01 S1 2013/05/05
2 K0001 契約者1B 2013/05/10 S1 2013/05/10
3 K0001 契約者1C 2013/05/20 S2 2013/05/25
4 K0002 契約者2A 2013/06/10 S2 2013/06/10
5 K0002 契約者2B 2013/06/10 S1 2013/06/15
6 K0002 契約者2C 2013/06/10 S2 2013/06/10
7 K0003 契約者3A 2013/07/01 S1 2013/07/05
8 K0003 契約者3B 2013/07/10 S2 2013/07/10
9 K0003 契約者3C 2013/07/10 S1 2013/07/10
1人の契約者が複数の契約種別のテータを持つ場合、テーブルは親子に分けます。親テーブルの主キーで子テーブルのデータを検索できるように子テーブルの主キーを決めます。
■親子にテーブルを分けた例
契約基本テーブル 主キー:基本契約番号(基本SEQ)
【基本契約番号】【契約番号】【契約者名】【基本契約年月日】
1 K0001 契約者1A 2013/05/01
2 K0001 契約者1B 2013/05/10
3 K0001 契約者1C 2013/05/20
4 K0002 契約者2A 2013/06/10
5 K0002 契約者2B 2013/06/10
6 K0002 契約者2C 2013/06/10
7 K0003 契約者3A 2013/07/01
8 K0003 契約者3B 2013/07/10
9 K0003 契約者3C 2013/07/10
契約明細テーブル 主キー:基本契約番号(基本SEQ)、明細番号
【基本契約番号】【明細番号】【契約種別】【明細契約年月日】
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
- 回答日時:
#1 です。
とりあえず、Access でも動く事は動いたようですね。
> 契約年月日の比較を優先し、1レコードに絞り込めなかった場合に基本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
- 回答日時:
以下でどうなりますか
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
- 回答日時:
手元に 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 契約者1A 2013/05/10
2 K0001 契約者1B 2013/05/01
3 K0001 契約者1C 2013/04/30
このような場合は、テーブル結合で下記の結果を得たいです。
【契約番号】【契約種別】【明細契約年月日】【基本SEQ】【契約者名】【基本契約年月日】
K0001 S1 2013/05/05 2 契約者1B 2013/05/01
K0001 S2 2013/05/10 1 契約者1A 2013/05/10
契約年月日の比較を優先し、1レコードに絞り込めなかった場合に基本SEQで
比較したいのです。
再度お知恵をかしていただけませんでしょうか?
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- 個人事業主・自営業・フリーランス 個人事業主が報酬額記載なしの基本契約書を結ぶメリットについて 6 2023/07/13 10:36
- 格安スマホ・SIMフリースマホ IIJmio スマホ大特価セール 1,980円 19,819円 クレジットカードで申請された 1 2022/10/10 05:20
- 労働相談 契約期間(退職日)の変更は問題ないのでしょうか。 2 2023/01/06 08:08
- 不動産業・賃貸業 賃貸の解約について質問です。 不動産会社の言うことが分からず質問させていただきました。 新築のマンシ 1 2022/08/06 19:35
- その他(住宅・住まい) 賃貸の解約について質問です。 不動産会社の言うことが分からず質問させていただきました。 新築のマンシ 6 2022/08/06 19:42
- 固定電話・IP電話・FAX NTT西日本新規契約について 1 2023/06/22 20:52
- Android(アンドロイド) ワイモバイル MNP 番号出しました 1月8 再契約で1月8の前にまたMNP 番号は出せるのか 1 2022/12/31 21:06
- 賃貸マンション・賃貸アパート 居住用賃貸アパートにサロン出店 4 2023/04/02 22:30
- SoftBank(ソフトバンク) ソフトバンク 携帯契約 について ソフトバンク1台(半年以上支払い済み本体代7万)ソフトバンク1台( 3 2022/11/09 22:19
- 借地・借家 住宅管理委託契約の合意更新をわすれた 2 2023/01/28 08:20
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
東京電力パワーグリッド株式会...
-
vio脱毛についてです 8年くらい...
-
契約の締結・終結の意味
-
月に27万円ほど払ってやれば性...
-
質問です。第一生命を辞めたい...
-
契約書によく出る文末の「もの...
-
契約社員の契約業務内容外の仕...
-
生命保険の女性外交員と食事(...
-
数学、割引率の問題【おそらく...
-
芸能事務所のマネジメント料っ...
-
業務委託での仕事について
-
障害者雇用のカウント方法につ...
-
代表取締役変更後の契約書の取...
-
20代でも使えなかったら、契約...
-
OCN モバイル ONE で OCNのSIM...
-
【企業間取引】取引基本契約 ...
-
傭船契約のRecapとは
-
第一生命の説明会に誘われたの...
-
皆さんは通信速度0になった時、...
-
契約書の書き方を教えてくださ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
東京電力パワーグリッド株式会...
-
質問です。第一生命を辞めたい...
-
PS5で10G
-
仕事における越権行為とは何が...
-
”請負”の反対語は?
-
聞こえるように悪口を言われた...
-
助けて下さい!!
-
雇用期間の定めあり(4ヶ月以上)...
-
芸能事務所のマネジメント料っ...
-
契約社員の契約業務内容外の仕...
-
契約の締結・終結の意味
-
生命保険の女性外交員と食事(...
-
傭船契約のRecapとは
-
月に27万円ほど払ってやれば性...
-
働いているところの校長先生が...
-
転職するべきか悩んでいます。
-
20代でも使えなかったら、契約...
-
最近マックでバイトを始めた者...
-
自己破産するとWi-Fiも契約でき...
-
三者契約(三社??)
おすすめ情報