ハガキ交換データのSQL文

質問

下記のような葉書のデータベースでのSQL分についてお尋ねします。

Parsonals データベースに、
年賀状と暑中見舞いとのデータテーブル(Cards)のほか、
個人データテーブル(Individuals)と
家族データテーブル(Family)と
住所データテーブル(Address)との４つのテーブルがあります。

４つのテーブルは、次のように構成されています。
Individuals, Family, Address, Cards
です。
１．Individuals テーブルは、
ID (オートナンバー), Name (文字列), FamilyID (数値), AddID (数値)
の４つのフィールド
２．Family テーブルは、
FamilyID (オートナンバー), FamilyName(文字列)
の２つのフィールド
３．Address テーブルは、
AddID (オートナンバー), Address(文字列), Telephone(文字列)
の３つのフィールド
４．Cards テーブルは、
CardID(オートナンバー), KindOfCard(YesNo), SendYear(数値 ヌル値許可),
ReceiveYear(数値 ヌル値許可), IndividualID (数値)
の５つのフィールド
です。

個人データすべてのほか、個人ごとの家族データすべて、個人ごとの住所データすべて、
そして、個人ごとのKindOfCard(年賀状か暑中見舞いか)ごとにSendYearデータのうち最新のものと
ReceiveYearデータのうちの最新のものを取得するSQL文を取得したいのです。
簡単にいえば、
個人個人に対して、その個々人の個人データ・家族データ・住所データのほか、
年賀状を送った最新の年と受けた最新の年、
暑中見舞いを送った最新の年と受けた最新の年を取得したいのです。
さらに、最新の送った年と最新の受けた年とが同一のときは、データが重複しますので一つに絞りたいです。
Cardsテーブルのフィールドには、SendYearもReceiveYearもありますので、単純にSendYearとReceiveYearを切り分けると当然データが重複するので、この重複は避けたいです。
このように毎年の葉書のやり取りを記録したデータから必要なデータを取り出したいというものです。私にとっては来年の年賀状を送るにあたって、どの人に出すべきかを判断する材料として、ぜひ取りだしたいビューなです。

かなり複雑なSQL文になるのかもしれませんが、どんなSQL文かを教えてください。
ぜひ、よろしくお願いします。

回答No.1

bin-chan · ベストアンサー

DBエンジンは何ですか？

お礼

すみません、データベースそのものを伝えていませんでした。

Access または、SQL Server のどちらかで教えてください。
また、説明不足でしたが、Individualsテーブルの.ID = CardsテーブルのID です。
その他、Individuals.AddID = Address.AddID 、
Individuals.FamilyID = Family.FamilyID という
外部キー関係です。

補足

申し訳ありません。

まず、
１）Where条件をKindOfCard＝Yesにして、IndividualID別CardID別別にmax(SendYear)とmax(ReceiveYear）を取得するクエリ
はどのようになるのでしょうか？

よろしくお願います。

回答No.2

bin-chan

Access(MDB)でもSQL Serverでも、フロントエンドはACCESSですね？

基本的にはデザインビューでクエリを作成して、SQLビューでSQL文表示するのが簡単でしょう。

テーブル４．Cards テーブルの構造からするとKindOfCardで暑中見舞い／年賀状を識別するんですかね？

１）Where条件をKindOfCard＝Yesにして、IndividualID別CardID別別にmax(SendYear)とmax(ReceiveYear）を取得するクエリを作成。
２）Where条件をKindOfCard＝Noにして、IndividualID別CardID別別にmax(SendYear)とmax(ReceiveYear）を取得するクエリを作成。
３）テーブル１～３を結合して送付先を取得するクエリを作成。
４）１）と２）と３）を結合（３のすべて、１・２はあるもの）年は４列アリ
５）１）２）のSendYearとReceiveYearを判断して、不要なものを表示しない。

の５段階で作成していくことになると思います。

「一文で表記したい」なら、１）～５）を纏めることで対応できそうですね。

補足

大変申し訳ありません。
非常に分かりづらい質問文であり、質問自体もあやふやなものでした。お詫び申し上げます。また、
＞ACCESS
でも今回のような複雑なものをSQLビューを使って扱う方法が分かりません。
＞KindOfCardで暑中見舞い／年賀状を識別
しています。

テーブルの構成をもう一度代えての質問となってしまい、恐縮です。
どれが外部キーなのかも分かりづらかったと思われ、申し訳ありません。
質問方法を変え、若干フィールドを変更します。

もう一度、４つのテーブルのフィールドを若干代えての質問になります。
１．Indvidualsテーブルは、ID, Name, FamilyID, AddID
２．Familyテーブルは、FamilyID, FamilyName
３．Addressテーブルは、AddID, Address, Tel
４．Cardsテーブルは、KindOfCard, CardYear, SendDate, ReceiveDate, ID
というフィールドを持っています。(SendYear, ReceiveYearを変えました、
(当初質問のフィールドと違って申し訳ありません)

これらの情報をもとに、
どこの誰に何年に年賀状を送ったのが最後だったか、どこの誰から暑中見舞いをもらったのが最後だったか、どこの誰からもらった年賀状は何年だったろうか？などを得て、来年に向けて年賀状を出したいと思っています。暑中見舞いもしかりです。(そのとき喪中だったかなどは今回除外しています)

単純に下記のようなクエリでは、もらったハガキが羅列されるだけなので、何年もたったデータがあると行が多くなりすぎて見づらくなります。

下記のようなSQL文では、数年越しのデータが表示されて、結局だれに送るべきかの判断をするには、行が多すぎてしまいます。
SELECT I.ID, I.FamilyID, I.AddID, I.Name, F.FamilyName, A.Address, A.Tel,
C.CardYear, C.KindOfCard, C.SendDate, C.ReceiveDate
FROM Family AS F INNER JOIN (Cards AS C
INNER JOIN (Address AS A INNER JOIN Individuals AS I ON A.AddID=I.AddID) ON C.ID=I.ID)
ON F.FamilyID=I.FamilyID;

で、ここで最近のハガキ以外を除きたいのです。(除外するという観点ではなくて、最近のデータを抽出するという観点です)
例）
Aさんは一昨年年賀状をくれた。けれど、Aさんには昨年年賀状を出したけれど返ってこなかった。
Bさんは昨年年賀状もくれたし、暑中見舞いもくれた。けれど、こちらからは昨年年賀状は送ったけれど、暑中見舞いは送らなかった。
など、最近のデータに限ったデータとして、Aさんとのハガキのやり取りの他にAさんの住所データや家族データなども含めたデータを得たいのです。

bin-chanさんのご回答は非常にありがたいのですが、なにしろ、初級者未満の身なので、Whereと結合とがゴッチャになって結局どんなクエリになるか分からないのです。(不勉強でごめんなさい)
大変申し訳ないのですが、SQLサンプルをいただきたいところです。（是非心からお願いします）

自分勝手でゴメンナサイ。能力不足のため答えから学びとらせてください。