【VBA備忘録】VBA SQLでテーブルを結合したらデータが増えた！？そんなときは結合したテーブルのキーを確認しよう。

素人VBA備忘録第7回です。

例によって個人的な備忘録ですが、もしかしたら役に立つかもしれないので公開しています。

抽出してみたら元のテーブルよりデータの数が増える？

あるデータをSQLで抽出する際には、1つのテーブルだけでは欲しいデータが取れない場合はそのデータを持つ他のテーブルを結合させてやることがあります。

テーブルAとテーブルBがあり、そもそもAの全データが欲しくてBで追加情報を得たい場合、

SELECT ○○ FROM A LEFT JOIN B ON A.●● = B.×× WHERE ～

という形でテーブルを結合させるコードを書くことが多いと思います。

しかしいざ実行してみるとAの全データが出てくるは出てくるのですが、なぜかAのデータが重複していくつも出力されて本来のデータ数を遥かに上回る膨大なデータが吐き出されることがあります。

泣きそうになりながらいろいろ調べた結果、この現象にはちゃんとした原因があるということがわかりましたのでそれついて書いていきますよ。

データが重複されて出力されて困る原因。

ちょっと例を出してみます。

下図のようなデータがあるとしましょう。

左側の伝票データに取引先名を追加したい場合、

strSQL = "SELECT 伝票データ.伝票番号, 伝票データ.取引先コード," & _
                "納品先マスタ.取引先名, 伝票データ.納品先コード, " & _
         　　　　"伝票データ.商品コード, 伝票データ.売上金額 " & _
         "FROM 伝票データ LEFT JOIN 納品先マスタ " & _
         "ON 伝票データ.取引先コード = 納品先マスタ.取引先コード"

といったSQL文を書きますよね。

しかしこれを実行すると出てくる結果は

となり、本来4つしかないはずの伝票データが重複しまくって大量に吐き出されてしまいます。

この例の場合、抽出データとして納品先名を増やしてやると少し原因が見えてきます。

strSQL = "SELECT 伝票データ.伝票番号, 伝票データ.取引先コード," & _
                "納品先マスタ.取引先名, 伝票データ.納品先コード, 納品先マスタ.納品先名 " & _
         　　　　"伝票データ.商品コード, 伝票データ.売上金額 " & _
         "FROM 伝票データ LEFT JOIN 納品先マスタ " & _
         "ON 伝票データ.取引先コード = 納品先マスタ.取引先コード"

 

伝票データ1つに対して、取引先コードに対応する納品先名がすべて出力されているのがわかります。
納品先が3つある山田商事の伝票データは全て3倍、納品先が4つある三本商店では全て4倍の量になってしまっていますね。

ここで改めて結合条件を確認してみます。

ON 伝票データ.取引先コード = 納品先マスタ.取引先コード

結合条件に取引先コードを指定しているのですが、テーブルの方を確認してみると、

納品先マスタでは取引先コード1つに対して3つないしは4つのデータを保持しています。
結合条件がデータを1つに限定できるものではない場合、全てのデータを引っ張ってくるため、その分元のテーブルのデータがどんどん増えていくというカラクリのようです。

LEFT JOINでテーブルを結合する場合、「結合条件は追加するテーブルのデータを限定するものでなくてはいけない」ということになります。

 

※ちなみに上で出した例は実際に動かしてみたものではなく、調べた結果を元に手作業で作ったものなので間違っていたら教えて下さい。

スポンサーリンク

SQLで抽出したらデータが増えてしまう場合の解決方法。

結合条件がプライマリキーではない場合、重複した分だけデータが増えてしまうというのはわかりました。
ではどのように解決したらいいでしょうか。

本来であればプライマリキーを探してそれを結合条件にすれば解決しますが、上記の例の場合、単独でデータを限定するようなデータは見当たりません。

この場合は、結合条件を増やすことで解決が可能です。

納品先マスタのテーブルをよく見ると、伝票データの中にある項目の中では取引先コードと納品先コードを組み合わせるとデータを各行1つに限定できそうです。

というわけでON文の条件を増やしてみたコードがこちら。

strSQL = "SELECT 伝票データ.伝票番号, 伝票データ.取引先コード," & _
                "納品先マスタ.取引先名, 伝票データ.納品先コード, 納品先マスタ.納品先名 " & _
         　　　　"伝票データ.商品コード, 伝票データ.売上金額 " & _
         "FROM 伝票データ LEFT JOIN 納品先マスタ " & _
         "ON 伝票データ.取引先コード = 納品先マスタ.取引先コード AND " & _
            "伝票データ.納品先コード = 納品先マスタ.納品先コード"

普通にON句の中身をANDでつなげてあげれば結合条件を増やすことが可能です。

こうすると追加テーブルのデータが限定され、バカみたいな数のデータが吐き出されずに済みますので、謎にデータが増えてしまう場合はまず結合条件がプライマリキーであるかどうかを確認してみるといいでしょう。

Accessでテーブルのプライマリキーを探す方法。

さて、上記の例ではデータが少ないためテーブルを見てプライマリキーらしき組合せを探すことができましたが、多数の項目を持つテーブルの場合はそうもいきません。

ただAccessであればプライマリキーを探すのは難しくありません。
というわけでAccessでキーを探してみましょう。

まずAccessでテーブルを表示させたら、

上のところを右クリックして「デザインビュー」を選択します。

 

するとデータの左側に鍵っぽいマークがついています。
これがプライマリキーのようです。
1つであればそれが単独でキーになりますし、2つ以上であればその組み合わせがキーとなります。

そのプライマリキーが結合元のテーブルにあればそれを結合条件にすればOKです。

もしそのキーとなるデータが結合元のテーブルに存在しない場合は、そのキーデータを持つ別のテーブルを先に結合させてから追加テーブルを結合する必要があります。

"SELECT ×× (FROM A LEFT JOIN Z ON A.×× = Z.××)" & _
           "LEFT JOIN B ON Z.〇〇 = B.〇〇"

このような感じです。
テーブル「Z」が両方に共通のプライマリキーを持つ踏み台のテーブルにあたります。

もしSQLで抽出した結果が増えて困っている場合は、まず追加するテーブルのプライマリキーを疑ってみて下さい。

スポンサーリンク

VBA SQLで抽出したらデータ数が増えてしまった話まとめ。

• 元のデータより抽出件数が爆増するのは怖いがバグではない。
• 原因はテーブル結合。
• ON句で結合する要素がプライマリキーじゃないのが原因。
• 結合先のテーブルのデータを確認してみよう。
• 結合要素に対してデータが複数あると増える。
• 解決するには結合する要素を増やす。
• ON句もANDでつなげることが可能。
• Accessならデザインビューで簡単に確認可能。

こんな感じです。

抽出マクロを走らせてみたらどえらい数の重複データが出てきてびっくりすることがありますが、原因さえわかれば修正はそれほど難しいものではありません。

結合にはユニークデータが必須

ということを学んだ一件でした。

いきなり重複データがごっそり出てきて泣きそうになっている人の助けになれば幸いでございますよ。

以上です！