Q&A
コードやテーブルのデータはコードブロックにしましょう。
お世話になります。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;";
何度も修正をしては確認を繰返しておりますが、解決の糸口が見えないための質問です。
お忙しい中恐縮ですが、アドバイスのほど、よろしくお願いいたします。
sqlのmailboxとある項目は、mailと読み替えて良いでしょうか？また値’goolee’はgoogleとしても良いでしか？
もう一箇所ですがcustomerにccc@の人が居ないのはよろしいのでしょうか？
flied_onion
flied_onion様
コードブロックの件、失礼いたしました。アドバイスの通り設定いたしました。
A.Ichi様
誤字失礼いたしました。仰る通りでございます。説明を簡素化するため実際のカラム名を一部省略して記載したかったのですが、見落としがあったようです。失礼しました。
回答2件
0
ベストアンサー
まず念頭に置いて欲しいのですが、
テーブルを結合してレコードが増殖する原因としては、
0. 結合キーが不足している
0. 一意だと思っていたデータにダブりがある
上記の2パターンしかありえません。
それを踏まえたうえで今回のクエリを見ると、
SQL
1SELECT 2 mailbox.mail_no 3 , mail.mail_account_id 4 , mail.mailbox_title 5 , mail.mailbox_address 6 , customer.cust_id 7 , customer.cust_account_id 8 , customer.cust_tanntousha_no 9 , customer.cust_name 10 , customer.cust_mail 11 , tanntousha.tanntou_no 12 , tanntousha.tanntou_account_id 13 , tanntousha.tanntou_name 14FROM 15 mail 16 LEFT JOIN customer 17 ON mail.mail_address = customer.cust_mail -- メールアドレスがダブっていると分裂 18 LEFT JOIN tanntousha 19 ON customer.cust_tanntousha_no = tanntousha.tanntou_no 20WHERE 21 mail.mail_account_id = 'google'
サンプルも誤っている可能性が高いですが、
顧客テーブルで同一メールアドレスデータが存在しているので、
これが正しいならここが分裂の要因と思われます。
もう1点気になったのがメールテーブルと顧客テーブルの結合キーについて、
アカウントIDは結合キーに含まれておりませんが、
これはつまりメールアカウントIDと顧客アカウントIDは一致しなくてもよいというルールとなっていますが正しい仕様なのでしょうか？
その点を確認した方が良いかもしれません。
###蛇足
顧客名が決まってもメールアドレスは一意とならない場合は以下読み飛ばしてください。
顧客テーブル上の顧客名、メールアドレスのペアは常に変わらない、
つまり顧客名が決まればメールアドレスが一意に定まる状況の場合、
今の顧客テーブルは1事実1箇所の原則に違反しており第1正規形の状態のテーブルとなるので、
可能であればもう一段階正規化を進めることをお勧めします。
(※更新時不整合を防止する意味で)
投稿2016/08/07 14:01
総合スコア1636
KotoriMaturi様
ご回答ありがとうございます。
「 一意だと思っていたデータにダブりがある」
まさにこれが原因のようです。
同じアカウント（質問でyahoo、googleと指しているところ）では同一アドレスは存在しませんが、別のアカウントを数えれば同じアドレスの存在があることになります。（結果、同一テーブル内に複数の同一アドレスが存在することになる）
このたびご指定いただけたこと感謝いたします。テストのデータ配分によっては見落とすところでした。また質問させて頂くこともあるかもしれませんが、その際はよろしくお願いいたします。
> chappさん
問題が解決されたようで何よりです。
今回のようなレコード増殖が発生するケースは、回答に記載した要因のいずれかなので、
今後同様な現象が出た際も、
・結合に用いる列データの確認
・結合キーの確認
を行う習慣付けを行うと良いですよ＾＾
KotoriMaturi様
ありがとうございます＾＾
今後ともよろしくお願いいたします！
0
「期待する結果が返ってきません」とありますが、何が起きているのかわかりません。
とりあえず期待する結果とselect文で指定している列の数は違うと思いますので、
mail.mail_title, mail.mail_address, customer.cust_name, tanntousha.tanntousha_tanntou_name
だけにしてみては。
投稿2016/08/07 11:50
総合スコア2604
早速の返信ありがとうございます。
また、こちらの説明不足申し訳ありません。
現状をお伝えすると、yahooとして呼び出すと、mailテーブルにあるデータに紐づくデータがcustomerにない場合、２つ（2行）出てきている状態で、他方、Googleで呼出すとcustomer、tanntoushaともに紐づくデータがあるが、1つしかないデータが２つ表示されている状況になっています。
SQLに見落としがないか何度も見直しては動作確認をしているのですが、いずれも解決には至らず質問させてもらった次第です。
引き続き、アドバイスのほどお願いできれば幸いです。
どう出てきているかも質問に追記してほしいです（質問の例のデータで実際にどうでるのか）。
あとコードブロック（ 対象を選択して </> ボタン）も使ってください。
あとは、LEFT JOINを LEFT OUTER JOIN にしてみてください。（１つしかないデータが２つというのは解消しないかもですが）
flied_onion様
色々とありがとうございます。ご指摘の通り、コードブロックに入れるため、テーブルの中身を見直しながら質問を修正してみましたが、結果、見直すことができ、結合キーに不適切なところがあったようです。
お忙しい中ご親切な対応ありがとうございました。見直すためいったんこちらの質問は解決とさせていただきます。ありがとうございました。
あなたの回答
tips
プレビュー
