Access2000のSQL文の質問です。

質問

Access2000のSQL文の質問です。(長文)

[OYA]テーブルに、OYA_CODE(KEY),OYA_NAMEのフィールドがあります。
　　データは、1 , A
2 , B です。

[KO]テーブルに、KO_CODE(KEY),KO_NAMEのフィールドがあります。
　　データは、11 , A-1
12 , A-2
13 , A-3
21 , B-1
22 , B-2 です。

[TENKAI]テーブルに、OYA_CODE(KEY),KO_CODE(KEY),CNTのフィールドがあります。
　　データは、1 , 11 , 1
1 , 12 , 2
2 , 21 , 3
2 , 22 , 4 です。

[DATA]テーブルに、OYA_CODE(KEY),KO_CODE(KEY),CNTのフィールドがあります。
　　データは、1 , 11 , 10
1 , 12 , 20
2 , 21 , 100
2 , 22 , 200 です。

結果を
OYA_CODE, OYA_NAME, KO_CODE, KO_NAME, KEI
1, A, 10
1, A, 11, A-1, 10
1, A, 12, A-2, 40
2, B, 100
2, B, 21, B-1, 300
2, B, 22, B-2, 800

と、したいのです。
つまり、[TENKAI]のCNT×[DATA]のCNT が、ほしいのです。
ネックは、OYA_CODEのみのレコードが出ないのです。

このテーブルにこう言うフィールドをつける等、
具体的にご支持を頂ければ幸いです。
宜しくお願い致します。

回答No.1

nda23

サブクエリを使うのかな・・・
Access2003では下記SQLで可能です。

SELECT E.OYA_CODE,F.OYA_NAME,E.KO_CODE,G.KO_NAME,E.KEI
FROM ((SELECT C.OYA_CODE,C.KO_CODE,C.CNT*D.CNT AS KEI
FROM DATA AS C INNER JOIN TENKAI AS D ON
C.OYA_CODE=D.OYA_CODE AND C.KO_CODE=D.KO_CODE
UNION
SELECT A.OYA_CODE,NULL AS KO_CODE,A.CNT AS KEI
FROM DATA AS A INNER JOIN
(SELECT X.OYA_CODE,MIN(X.KO_CODE) AS KO_CODE FROM DATA AS X
GROUP BY X.OYA_CODE) AS B
ON A.OYA_CODE=B.OYA_CODE AND A.KO_CODE=B.KO_CODE) AS E
LEFT JOIN OYA AS F ON E.OYA_CODE=F.OYA_CODE)
LEFT JOIN KO AS G ON E.KO_CODE=G.KO_CODE

何やってるか分からないですね。
(1)DATAテーブルからOYA_CODEと、OYA_CODEでグループ化した時の
　最小値のサブクエリを作ります。
(2)DATAテーブルと(1)のサブクエリをINNER JOINし、CNTと共に
　KO_CODEをNULLとした（KO_CODEとKO_NAMEを空欄にするため）
　クエリ（「OYA_CODEのみのレコード」）を作っています。
(3)DATAテーブルとTENKAIテーブルをOYA_KEYとKO_KEYでINNER JOINし、
　DATA.CNT * TENKAI.CNT をKEIとするクエリを作っています。
(4)上記(2)と(3)を合成（UNION）したものと、OYA、KOをそれぞれ
　LEFT JOIN してOYA_NAMEとKO_NAMEを持ってきています。

分かりにくい場合は各"SELECT"の所を別クエリにして結合しても同じ
結果が得られます。「OYA_CODEのみのレコード」は「1,A,10」には
ならず、「1,A,,,10」になります。まあ、これは当然ですね。

お礼

早速のご回答をありとがとうございます。
テスト環境では、ACCESS97の為だと思いますが、
「FORM句の構文エラー」と、なってしまいます。
明日、改めて結果をご報告できるかと思います。
宜しくお願い致します。