質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.34%

3つのテーブルとの外部結合とcount()の期待値があいません

解決済

回答 3

投稿

  • 評価
  • クリップ 0
  • VIEW 6,927

score 161

こんにちは、いつもお世話になっております。

3つのテーブルを外部結合して、group byしたカラム(今回の場合,userId)ごとのcount件数が合わなくて困っています。
現状今のテーブル構成は以下の通りとなっています。
カラム[id]はどのテーブルもauto incrementされたものとなっています。

◆アカウントTBL
|id|userId|userName|
|01|000001|testuser1|
|02|000002|testUser2|
|03|000003|testUser3|
|04|000004|testUser4|

◆投稿TBL
|id|toukouId|userId|toukouText|
|01|01010101|000001|aaaaaa|
|02|03030303|000003|bbbbbb|
|03|01010102|000001|cccccc|
|04|02020202|000002|dddddd|

◆投稿画像TBL(1つの投稿につき複数枚の画像が投稿可能です。また、画像なしの投稿もありえます)
|id|toukouId|userId|imgFile|
|01|01010101|000001|img1|
|02|01010101|000001|img2|
|03|01010102|000001|img3|
|04|02020202|000002|img4|

SELECT文の期待する結果としては以下のようになっています。
◆期待結果
|userId|userName|count(投稿数)|count(画像数)|
|000001|testuser1|2|3|
|000002|testUser2|1|1|
|000003|testUser3|1|0|
|000004|testUser4|0|0|

ただ、3つ以上のテーブルを外部結合して同時に取得したいのですがcount(投稿数、画像数)の値が異常に大きな数値が返ってきます。

テーブル2つの外部結合では正確な値が取得できるのですが、3つめを外部結合した瞬間にダメになります。
参考として試したSQL文を以下に記載します。

◆2つのテーブルだけを外部結合した際のSQL(正確な値が表示されるパターン)
SELECT U.userId, U.userName, COUNT(T.toukouId) as tCount
     FROM アカウントTBL U LEFT JOIN 投稿TBL T ON U.userId = T.toukouId group by U.userId

◆3つのテーブルを外部結合した際のSQL(異常な値が返されるパターン)
SELECT U.userId, U.userName, COUNT(T.toukouId) as tCount COUNT(I.toukouId) as iCount 
    FROM アカウントTBL U LEFT JOIN 投稿TBL T ON U.userId = T.userId 
          LEFT JOIN 投稿画像TBL I ON U.userId = I.userId group by U.userId 

基本的に、3つ目の同じように外部結合すればいいだけだと思っていたのですが・・・
調べてからかなりの時間がかかっているので、ご教授お願いいたします。

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 3

checkベストアンサー

+1

FROM アカウントTBL U LEFT JOIN 投稿TBL T ON U.userId = T.userId 

|id|userId|userName|id|toukouId|userId|toukouText| 
|01|000001|testuser1|01|01010101|000001|aaaaaa| 
|01|000001|testuser1|03|01010102|000001|cccccc| 

|02|000002|testUser2|04|02020202|000002|dddddd| 

|03|000003|testUser3|02|03030303|000003|bbbbbb| 

|04|000004|testUser4|null|null|null|null|

LEFT JOIN 投稿画像TBL I ON U.userId = I.userId

|id|userId|userName|id|toukouId|userId|toukouText|id|toukouId|userId|imgFile| 
|01|000001|testuser1|01|01010101|000001|aaaaaa|01|01010101|000001|img1| 
|01|000001|testuser1|01|01010101|000001|aaaaaa|02|01010101|000001|img2| 
|01|000001|testuser1|01|01010101|000001|aaaaaa|03|01010102|000001|img3|
|01|000001|testuser1|03|01010102|000001|cccccc|01|01010101|000001|img1|  
|01|000001|testuser1|03|01010102|000001|cccccc|02|01010101|000001|img2| 
|01|000001|testuser1|03|01010102|000001|cccccc|03|01010102|000001|img3|

|02|000002|testUser2|04|02020202|000002|dddddd|04|02020202|000002|img4| 

|03|000003|testUser3|02|03030303|000003|bbbbbb|null|null|null|null| 

|04|000004|testUser4|null|null|null|null|null|null|null|null|

SELECT U.userId, U.userName, COUNT(T.toukouId) as tCount, COUNT(I.toukouId) as iCount
group by U.userId 

|userId|userName|count(投稿数)|count(画像数)| 
|000001|testuser1|6|6|
|000002|testuser2|1|1|
|000003|testUser3|1|0|
|000004|testUser4|0|0|

と、なるわけです。

SELECT A.userId, A.userName, A.tCount, B.iCount FROM
(
  SELECT U.user_id, U.user_name, COUNT(T.toukou_id) as tCount FROM アカウントTBL U 
  LEFT JOIN 投稿TBL  T USING(userId)
  GROUP BY U.user_id
) as A
LEFT JOIN (
  SELECT U.userId, COUNT(I.toukouId) as iCount FROM アカウントTBL U 
  LEFT JOIN 投稿画像TBL I USING(userId)
  GROUP BY U.user_id
) AS B
USING(userId)

汚いSQLですみませんが、こんな感じで期待値です。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2015/04/10 12:30

    すごい分かりやすい説明、誠にありがとうございます!
    また、こんなに早くご回答いただけるなんて!ありがとうございます。

    ご教授いただいたSQLを自分のプログラム用に修正したところ期待通りの結果が得られました!

    キャンセル

0

結合条件が足りていないので
投稿TBLの件数×投稿画像TBLの件数になっているように思えます
SELECT
      U.userId
    , U.userName
    , COUNT(DISTINCT T.toukouId) as tCount
    , COUNT(I.toukouId) as iCount 
FROM アカウントTBL U
LEFT JOIN 投稿TBL T
ON U.userId = T.userId 
LEFT JOIN 投稿画像TBL I
ON T.toukouId = I.toukouId    -- <- この条件が足りてない
AND U.userId = I.userId       -- <- たした条件で絞り込めているのでなくてよい
group by
      U.userId
    , U.userName 
こんなSQLでいかがでしょうか?

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2015/04/10 13:31

    せっかく各テーブルにuserIdがあるのでこっちのほうがシンプルかもしれません
    ```lang-<SQL>
    SELECT
    U.userId
    , U.userName
    , T.tCount
    , I.iCount
    FROM アカウントTBL U
    LEFT JOIN (
    SELECT
    userId
    , count(*) as tCount
    FROM 投稿TBL
    GROUP BY
    userId
    ) as T
    ON U.userId = T.userId
    LEFT JOIN (
    SELECT
    userId
    , count(*) as iCount
    FROM 投稿画像TBL
    GROUP BY
    userId
    ) as I
    ON U.userId = I.userId
    ```

    キャンセル

0

操作を間違えました。。。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.34%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

同じタグがついた質問を見る

閲覧数の多いPHPの質問