SQLの作成について

質問

以下のSQLが作りたいのですが
作れなくて困っています。
有識者の方、お助けください。

環境：SqlServer2008

★詳細説明
テーブルA、B、Cがあるとします。

A-B １対１
B-C は１対N

A,B,Cは全て検索・表示対象になります。

A-Bに対する検索表示は問題ないのですが
Cの発送区分(1:未発送 2：発送済み)を検索条件と
するSQL作成に悩んでおります。

★テーブル・構成データ
A
年度 顧客CD 顧客名称
2010 1000 A社
2010 1001 B社
2010 1002 C社
2010 1003 D社

B
年度 顧客CD サービス開始日
2010 1000 　2011/04/1
2010 1001 　2011/04/2
2010 1002 　2011/04/3
2010 1003 　2011/04/4
C
年度 顧客CD 部署 発送区分 発送日
2010 1000 A 1
2010 1001 A 1
2010 1001 B 2 2010/10/11
2010 1001 C 2 2010/10/12
2010 1003 D 1
2010 1003 D 2 2010/10/20

★実現したいSQL
1.発送区分に検索条件を設定しない場合
　　年度 顧客CD 発送区分 発送日
　　2010 1000 1
　　2010 1001 2 2010/10/12
　　2010 1002 1
　　2010 1003 2 　　　　2010/10/20

2.発送区分を未発送

　　年度 顧客CD 発送区分 発送日
　　2010 1000 1
　　2010 1002 1

3.発送区分を発送済み

　　年度 顧客CD 発送区分 発送日
　　2010 1001 2 2010/10/12
　　2010 1003 2 2010/10/20

1～3を1回のSQLで実行したいのですが
実現できておりません。

よろしくお願いします。

回答No.3

yamada_g

先ほどのSQLをサブクエリなりVIEWなりにして、その外側で条件を指定すればいいのではないでしょうか。

select *
from (先ほどのSQL)
where 発送区分 = 1;

という感じで。

お礼

遅くなりました。

解決しました。
親切な対応、ありがとうございました。

回答No.2

yamada_g

>データが無い場合は未発送として扱いたいです。
これでどうでしょうか。

select
　A.年度,A.顧客CD,coalesce(C.発送区分,1) 発送区分,C.発送日
from A
　inner join B on (A.年度 = B.年度 and A.顧客CD = B.顧客CD)
　left join (select 年度, 顧客CD, 発送区分, 発送日,
　　　　　　　　　　row_number() over(partition by 年度,顧客CD order by 発送区分 desc, 発送日 desc) rnum
　　　　　　　from C) C on (B.年度 = C.年度 and B.顧客CD = C.顧客CD and C.rnum = 1);

ただ、先ほどの回答にも
実際に存在しない場合は外部結合に変更して発送区分がnullの時は1に置き換える処理が必要です。
と書きました。
ご自身では何もされないのでしょうか・・・
こう修正したけど上手くいきませんでした、とかなら分かりますが。

補足

解答が至らず申し訳ございません。
私の知識ですとBとCをLEFT JOINしますと、Cの発送区分に抽出条件を設定しても
全て表示されてしまうのでは？と考えました。
発送区分に対してのWHERE句はFROM　Cの後ろを想定していました。
認識違いがあるようでしたらご教授下さい。

回答No.1

yamada_g

SQLを考えるのに必要な条件が全然足りないように思います・・
Cのにある年度・顧客CDでの複数データから1.のように1件に絞り込む条件として
発送済データがあれば最新の発送日を取得するのかなと妄想しました。

select
　A.年度,A.顧客CD,C.発送区分,C.発送日
from A
　inner join B on (A.年度 = B.年度 and A.顧客CD = B.顧客CD)
　inner join (select 年度, 顧客CD, 発送区分, 発送日,
　　　　　　　　　　row_number() over(partition by 年度,顧客CD order by 発送区分 desc, 発送日 desc) rnum
　　　　　　　from C) C on (B.年度 = C.年度 and B.顧客CD = C.顧客CD and C.rnum = 1);

このSQLで1.の結果になると思います。
あとは上記のwhere句に発送区分の条件を追加すれば2.と3.も取得できると思います。
ただ、Cに顧客CD：1002のデータがないので記載漏れと仮定しているので、
実際に存在しない場合は外部結合に変更して発送区分がnullの時は1に置き換える処理が必要です。

>1～3を1回のSQLで実行したいのですが
1回のSQLでという意味もちょっと分からないので見当違いの答えかもしれません。
そうでしたら申し訳ありません・・

補足

解答ありがとうございます。 また説明不足で申し訳ございません
記載して頂いたものが私が作成したい。splです。
一点、違いますのはcの顧客CD：1002は記載漏れではなく、データが無い場合は未発送として扱いたいです。

お手数ですがよろしくお願いします。