アクセス2003 SQL2000を使用してます。

質問

いつもお世話になっています。
下記のようにAテーブルBテーブルを元に
Cテーブルのようにデータを出力したいのですが、
SQL文がうかびません。
どなたか教えてください！！

●Aテーブル(取込データ）
　名前　　｜　コード1（主コード）
-------------------------
　小林　　｜　100　
　小林　　｜　102　　
　田中　　｜　110　
　町田　　｜　130
　木村　　｜　160
　木村　　｜　160
　木村　　｜　180

●Bテーブル（取込データ）
コード1 ｜　コード2
---------------------------
　100　　　｜　A
　110　　　｜　C　
　130　　　｜　D
　160　　　｜　E

●Cテーブル（出力データ/ABテーブル合体させたもの）
★　※※※を表示させたい！

　↓ここまでは表示はできている↓

　名前　　｜　コード1　　｜　コード2
--------------------------------------------
　小林　　｜　100　　　　｜　A
　小林　　｜　102　　　　｜　※※※
　田中　　｜　110　　　　｜　C　
　町田　　｜　130　　　　｜　D
　木村　　｜　160　　　　｜　E
　木村　　｜　160　　　　｜　E
　木村　　｜　180　　　　｜　※※※

　↓表示したい内容。不明な点。↓

　名前　　｜　コード1　　｜　コード2
--------------------------------------------
　小林　　｜　100　　　　｜　A
　小林　　｜　102　　　　｜　A
　田中　　｜　110　　　　｜　C　
　町田　　｜　130　　　　｜　D
　木村　　｜　160　　　　｜　E
　木村　　｜　160　　　　｜　E
　木村　　｜　180　　　　｜　E

以上　よろしく御願いします！！

回答No.3

jamshid6 · ベストアンサー

#1です。

>要は、名前に対して、コード１は複数ある可能性があり、
>コード２は１つしか存在しない、のです。

「名前からコード２は一意に決まる」、これが仕様であるということならば、
SELECT
a.名前,
a.コード1,
ISNULL(b.コード2,
(SELECT コード2 FROM BTABLE
WHERE コード1 IN
(SELECT コード1 FROM ATABLE
WHERE 名前=a.名前))
)
FROM ATABLE a
LEFT OUTER JOIN BTABLE b ON b.コード1=a.コード1

なんて書き方もできますが、SQL Server 2005以降ならば以下の方法が一番楽でしょう。

SELECT
a.名前,
a.コード1,
MAX(b.コード2) OVER (PARTITION BY a.名前)
FROM ATABLE a
LEFT OUTER JOIN BTABLE b ON b.コード1=a.コード1

お礼

お礼が遅くなり大変申し訳ございません。

教えていただいたもので出来ました！

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

回答No.2

3rd_001

この仕様だと「名前」がキーにならざるをえないですよね？
「名前」で一意になりますか？
また、同姓同名の場合はどうするのでしょうか。

[小林　100]と[小林　102]が同一人物と保証するキー項目はないのでしょうか。

この点をクリアできなければ、正しいデータはセットできませんよ。

補足

ご回答ありがとうございます。
そうです、名前がキーになります。
しかし、その他、社名や住所で一致させることも可能です。

回答No.1

jamshid6

一つ例を書いておきますが、理解頂いた上で、必要な変更を掛けて頂く必要があります。
なぜなら、Aテーブルに(町田, 120)のデータがあったと仮定すると、どう出したいかわからないからです。
（つまり、仕様が明確に記載されていないということです）

とりあえずは、「１つ前の有効なコードをセットする」と判断することにします。
この場合、(町田, 120)があった場合のコード2は'C'になります。NULLのままにしたいとか、'D'にしたいとかであれば、変更が必要です。

SELECT
a.名前,
a.コード1,
ISNULL(b.コード2,
(SELECT コード2 FROM BTABLE WHERE コード1 IN
(SELECT MAX(コード1) FROM BTABLE WHERE コード1<a.コード1)
)
)
FROM ATABLE a
LEFT OUTER JOIN BTABLE b ON b.コード1=a.コード1

補足

早速のご回答ありがとうございました。

今回のテーブルはNULL不可なので、その点は大丈夫なのですが、
（町田,120）であった場合、'Ｄ'に表示したいのです（＞＜）

要は、名前に対して、コード１は複数ある可能性があり、
コード２は１つしか存在しない、のです。

説明が下手で申し訳ございません。。