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

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

ただいまの
回答率

88.81%

JOINについて 欲している結果が返ってきません。

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 1,247

chapp

score 219

お世話になります。MySQLのJOINに関して質問させてください。

現在、勉強がてらに社内で利用しているメールを1元管理するようなシステムを作っています。
送受信したメール情報はDBに保存いたしますが、そのデータを呼び出す際に、躓いてしまい質問させていただきました。

今回のメール機能におけるテーブルは以下、3つです。(本質問において不必要なカラムは省略いたします。文中にあるyahooとgoogleに意味はありません)

●mail(メールのデータを残しておくテーブル)

mail_no | mail_account_id | mail_title | mail_address
--------------------------------------------------------------
  1      yahoo       件名1    aaa@***.com
  2      yahoo       件名2    bbb@***.com
  3      yahoo       件名3    bbb@***.com
  4      yahoo       件名4    ccc@***.com
  5      google       件名5    ccc@***.com

■customer(顧客情報が登録されているテーブル。mailテーブルとはメールアドレスで紐づいているが、はじめての顧客などの場合は顧客情報がない場合がある)

cust_id | cust_account_id | cust_tanntousha_no | cust_name | cust_mail
--------------------------------------------------------------------------------
  1      yahoo        2        森       bbb@***.com
  2      yahoo        0        藤田        ccc@***.com
  3      google        1        藤田      ccc@***.com

●tanntousha(顧客につく担当者テーブル。顧客テーブルのcust_tanntousha_noにて紐づいている)

tanntou_no | tanntou_account_id | tanntou_name
------------------------------------------------------
  1        yahoo        小島
  2        google        渡辺

以上のような内容ですが、欲している結果と現状を報告すると」mailテーブルを基に、yahooで呼出した場合

件名 | アドレス | 名前 | 担当者名
-----------------------------------------------
件名1 aaa@***.com  ---    ----
件名2 bbb@***.com  森    小島
件名3 bbb@***.com  森    小島
件名4 ccc@***.com  藤田   ---

となるようしたいが、現在は、

件名 | アドレス | 名前 | 担当者名
-----------------------------------------------
件名1 aaa@***.com  ---    ----
件名2 bbb@***.com  森    小島
件名3 bbb@***.com  森    小島
件名4 ccc@***.com  藤田   ---
件名4 ccc@***.com  藤田   渡辺

となっており、他方、googleで呼出した場合、

件名 | アドレス | 名前 | 担当者名
-----------------------------------------------
件名5 ccc@***.com  藤田   渡辺

となるようしたいが、現在は、

件名 | アドレス | 名前 | 担当者名
-----------------------------------------------
件名5 ccc@***.com  藤田   ---
件名5 ccc@***.com  藤田   渡辺

となっています。

なお、上記の結果を返している現在のSQLは、(mailテーブルにあるデータは、顧客情報にないメールがあり、さらには担当者がないメールもある)、

$sql = "SELECT mailbox.mail_no, 
mail.mail_account_id, 
mail.mailbox_title, 
mail.mailbox_address, 
customer.cust_id, 
customer.cust_account_id, 
customer.cust_tanntousha_no, 
customer.cust_name, 
customer.cust_mail, 
tanntousha.tanntou_no, 
tanntousha.tanntou_account_id, 
tanntousha.tanntou_name 
FROM mail LEFT JOIN customer ON 
mail.mail_address = customer.cust_mail 
LEFT JOIN tanntousha ON 
customer.cust_tanntousha_no = tanntousha.tanntou_no 
WHERE 
mail.mail_account_id = 'yahoo' 
ORDER BY $order LIMIT $st, $lim;";

何度も修正をしては確認を繰返しておりますが、解決の糸口が見えないための質問です。
お忙しい中恐縮ですが、アドバイスのほど、よろしくお願いいたします。

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

質問への追記・修正、ベストアンサー選択の依頼

  • flied_onion

    2016/08/07 20:42

    コードやテーブルのデータはコードブロックにしましょう。

    キャンセル

  • A.Ichi

    2016/08/07 22:02

    sqlのmailboxとある項目は、mailと読み替えて良いでしょうか?また値’goolee’はgoogleとしても良いでしか?

    キャンセル

  • A.Ichi

    2016/08/07 22:23

    もう一箇所ですがcustomerにccc@の人が居ないのはよろしいのでしょうか?

    キャンセル

  • chapp

    2016/08/07 23:11



    flied_onion

    flied_onion様
    コードブロックの件、失礼いたしました。アドバイスの通り設定いたしました。

    A.Ichi様
    誤字失礼いたしました。仰る通りでございます。説明を簡素化するため実際のカラム名を一部省略して記載したかったのですが、見落としがあったようです。失礼しました。

    キャンセル

回答 2

checkベストアンサー

+1

まず念頭に置いて欲しいのですが、
テーブルを結合してレコードが増殖する原因としては、

  1. 結合キーが不足している
  2. 一意だと思っていたデータにダブりがある

上記の2パターンしかありえません。

それを踏まえたうえで今回のクエリを見ると、

SELECT
      mailbox.mail_no
    , mail.mail_account_id
    , mail.mailbox_title
    , mail.mailbox_address
    , customer.cust_id
    , customer.cust_account_id
    , customer.cust_tanntousha_no
    , customer.cust_name
    , customer.cust_mail
    , tanntousha.tanntou_no
    , tanntousha.tanntou_account_id
    , tanntousha.tanntou_name 
FROM
    mail 
    LEFT JOIN customer 
        ON mail.mail_address = customer.cust_mail  -- メールアドレスがダブっていると分裂
    LEFT JOIN tanntousha 
        ON customer.cust_tanntousha_no = tanntousha.tanntou_no 
WHERE
    mail.mail_account_id = 'google'

サンプルも誤っている可能性が高いですが、
顧客テーブルで同一メールアドレスデータが存在しているので、
これが正しいならここが分裂の要因と思われます。

もう1点気になったのがメールテーブルと顧客テーブルの結合キーについて、
アカウントIDは結合キーに含まれておりませんが、
これはつまりメールアカウントIDと顧客アカウントIDは一致しなくてもよいというルールとなっていますが正しい仕様なのでしょうか?

その点を確認した方が良いかもしれません。

蛇足

顧客名が決まってもメールアドレスは一意とならない場合は以下読み飛ばしてください。

顧客テーブル上の顧客名、メールアドレスのペアは常に変わらない、
つまり顧客名が決まればメールアドレスが一意に定まる状況の場合、
今の顧客テーブルは1事実1箇所の原則に違反しており第1正規形の状態のテーブルとなるので、
可能であればもう一段階正規化を進めることをお勧めします。
(※更新時不整合を防止する意味で)

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/08/07 23:29

    KotoriMaturi様

    ご回答ありがとうございます。

    「 一意だと思っていたデータにダブりがある」

    まさにこれが原因のようです。
    同じアカウント(質問でyahoo、googleと指しているところ)では同一アドレスは存在しませんが、別のアカウントを数えれば同じアドレスの存在があることになります。(結果、同一テーブル内に複数の同一アドレスが存在することになる)

    このたびご指定いただけたこと感謝いたします。テストのデータ配分によっては見落とすところでした。また質問させて頂くこともあるかもしれませんが、その際はよろしくお願いいたします。

    キャンセル

  • 2016/08/07 23:56

    > chappさん
    問題が解決されたようで何よりです。
    今回のようなレコード増殖が発生するケースは、回答に記載した要因のいずれかなので、
    今後同様な現象が出た際も、
     ・結合に用いる列データの確認
     ・結合キーの確認
    を行う習慣付けを行うと良いですよ^^

    キャンセル

  • 2016/08/08 00:07

    KotoriMaturi様

    ありがとうございます^^
    今後ともよろしくお願いいたします!

    キャンセル

0

「期待する結果が返ってきません」とありますが、何が起きているのかわかりません。

とりあえず期待する結果とselect文で指定している列の数は違うと思いますので、

mail.mail_title,
mail.mail_address,
customer.cust_name,
tanntousha.tanntousha_tanntou_name


だけにしてみては。

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/08/07 21:07

    早速の返信ありがとうございます。
    また、こちらの説明不足申し訳ありません。

    現状をお伝えすると、yahooとして呼び出すと、mailテーブルにあるデータに紐づくデータがcustomerにない場合、2つ(2行)出てきている状態で、他方、Googleで呼出すとcustomer、tanntoushaともに紐づくデータがあるが、1つしかないデータが2つ表示されている状況になっています。

    SQLに見落としがないか何度も見直しては動作確認をしているのですが、いずれも解決には至らず質問させてもらった次第です。

    引き続き、アドバイスのほどお願いできれば幸いです。

    キャンセル

  • 2016/08/07 21:26

    どう出てきているかも質問に追記してほしいです(質問の例のデータで実際にどうでるのか)。
    あとコードブロック( 対象を選択して </> ボタン)も使ってください。

    キャンセル

  • 2016/08/07 21:28

    あとは、LEFT JOINを LEFT OUTER JOIN にしてみてください。(1つしかないデータが2つというのは解消しないかもですが)

    キャンセル

  • 2016/08/07 23:32

    flied_onion様

    色々とありがとうございます。ご指摘の通り、コードブロックに入れるため、テーブルの中身を見直しながら質問を修正してみましたが、結果、見直すことができ、結合キーに不適切なところがあったようです。

    お忙しい中ご親切な対応ありがとうございました。見直すためいったんこちらの質問は解決とさせていただきます。ありがとうございました。

    キャンセル

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

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

関連した質問

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