JOINでの結果取得　サブクエリをJOINする

前へ 次へ
このQ&Aのポイント
  • WordPressのデータベースでテーブル2つを結合してメール、名前、登録日時を取得する方法について教えてください。
  • 問題のSQLでは、wp_usersテーブルとwp_usermetaテーブルをJOINして結果を取得していますが、正しいSQLではなく、実行時間も長くなってしまっています。
  • ユーザー名を持っていないユーザーも取得する方法や、実行時間を短縮する方法を教えてください。
回答を見る
  • ベストアンサー

JOINでの結果取得　サブクエリをJOINする

WordPressのデータベースでテーブル2つを結合して以下の情報を取得しようとしています メール(wp_users.user_email)|名前(wp_usermeta.meta_value)|登録日時(wp_users.user_registered ) 123@a.com,山田,2012-06-17 12:33:44 456@b.jp,田中,2012-06-21 07:26:54 789@c.ne.jp,\N,2009-08-11 12:19:00 1234@d.ne.jp,\N,2009-08-11 12:19:00 321@x.ne.jp,\N,2009-08-11 12:19:00 ・ユーザーはwp_usersテーブルにeメール情報(user_email)を必ず持ている ・ユーザーの名前はwp_usermetaテーブルのwp_usermeta.meta_keyがuser_nameなレコードに保存されているが、ユーザーによってあるものとないものがある。無い場合はNULLにする。 テーブル構造は添付した画像に記載されています。 ■ユーザ数 mysql> SELECT COUNT(*) FROM wp_users; 18290 まずwp_usersとwp_usermetaをLEFT JOINしてmeta_key = 'user_name'なレコードを取得しようとしましたが、user_nameを持っているユーザーのみしか取得できませんでした。 ■user_nameを持っていないユーザーが取得できない SELECT u.user_email, m.meta_value FROM wp_users AS u LEFT JOIN wp_usermeta AS m ON u.ID = m.user_id WHERE m.meta_key = 'user_name'\G 2075 rows in set (0.00 sec) 次にサブクエリをJOINしました。結果は正しいものを取得できたのですが、実行時間が40秒以上かかってしまい実用性がありません。 ■欲しい結果（速度が遅い） SELECT u.user_email, a.meta_key, a.meta_value, u.user_registered FROM wp_users AS u LEFT JOIN (select * from wp_usermeta where meta_key = 'user_name') as a ON u.ID = a.user_id; 18290 rows in set (45.45 sec) この結果を取得できる、正しいSQLを教えてください。よろしくお願いします。

画像を拡大する
  • MySQL
  • 回答数1
  • ありがとう数1

質問者が選んだベストアンサー

  • ベストアンサー
  • yambejp
  • ベストアンサー率51% (3827/7415)
回答No.1

いまいち構造がわからないのですが whereじゃなくて連結の条件にしてみては？ SELECT u.user_email, m.meta_value FROM wp_users AS u LEFT JOIN wp_usermeta AS m ON u.ID = m.user_id and m.meta_key = 'user_name'

marujiku
質問者

お礼

ありがとうございました。 速度も圧倒的にこちらが速かったです。 18290 rows in set (2.22 sec) ONにANDをつけられることを初めて知りました・・ ネットで検索・質問の前にリファレンスを読んでおきます

  1. カテゴリ
  2. [技術者向] コンピューター
  3. データベース
  4. MySQL

関連するQ&A

  • inner joinでサブクエリ

    SQLについてお教え頂けませんでしょうか？ 在庫テーブル ・ID ・数量 データ 001,1 001,3 002,3 002,2 002,4 003,2 商品テーブル ・ID データ 001 002 003 004 やりたいこと： 在庫テーブルには重複しているIDがあります。 商品テーブルには重複しているIDはありません。 在庫テーブルの重複を削除したIDと商品テーブルのIDをinner joinで結びたいと思います。 ・select distinct ID FROM 在庫テーブル ・select ID FROM 商品テーブル この2つのjoin方法がわかりません。 結果として、 001 002 003 を取得したいいです。宜しくお願いします。 ちなみに select ID from 商品テーブル inner join 在庫テーブル 商品テーブル.ID = exists(select distinct ID from 在庫テーブル) というコードを書きましたが、動きませんでした・・・抽出データが0件でした・・・

  • サブクエリ内で INNER JOIN は

    グルーピング内で、あるカラムの最大値のレコードで絞り込む方法は、 以下のサイトで知ることができましたが。 http://www.yokablo.com/20120905717.html tb_score +-------+-------+-------+ | c_name | game | c_score | +-------+-------+-------+ | 山田 | 高飛び | 90 | +-------+-------+-------+ | 山田 | 徒競走 | 85 | +-------+-------+-------+ | 山田 | 玉入れ | 90 | +-------+-------+-------+ | 鈴木 | 徒競走 | 85 | +-------+-------+-------+ | 鈴木 | 騎馬戦 | 50 | +-------+-------+-------+ | 室伏 | 綱引き | 100 | +-------+-------+-------+ | 室伏 | 玉入れ | 10 | +-------+-------+-------+ SQL文： SELECT * FROM tb_score INNER JOIN ( SELECT c_name, MAX(c_score) AS MAXSCORE FROM tb_score GROUP BY c_name ) AS TBDUMMY1 USING (c_name) WHERE c_score = MAXSCORE 私の場合、t_score の親テーブル名`t_company`と、 子テーブル`t_items`がありまして、 以下のようなことをやりたかったのですが、エラーとなります。 上記SQL をサブクエリにします。 select * from ( SELECT * FROM tb_score INNER JOIN ( SELECT c_name, MAX( c_score ) AS MAXSCORE FROM tb_score GROUP BY c_name ) AS TBDUMMY1 USING ( c_name ) INNER JOIN t_company ON t_company.c_name = tb_score.c_name WHERE c_score = MAXSCORE ) as T1 , t_items where T1.id_score = t_items.id_score 結果エラーメッセージは、 #1060 - Duplicate column name 'c_name' でした。 エラー箇所が、 GROUP BY c_name　か、 USING ( c_name )　か、 INNER JOIN t_company ON t_company.c_name = tb_score.c_name なのか、分かりません。 SQL の規則としてやってはいけないことをやっていると想像してますが、 ご指摘・ご指導いただけると大変に助かります。 よろしくお願いします。

    • ベストアンサー
    • MySQL
  • Access 2000 サブクエリとJOIN

    Access2000でSQLを発行したのですが、 エラーが出てしまいます。 テーブル（仮にtest1、test2）があるとして test1 ------------------------- 主キー ｜ 番号｜項目 　　　1｜　　1｜りんご 　　　2｜　　2｜ぶどう 　　　3｜　　3｜みかん 　　　4｜　　4｜いちご test2 ----------------------------- 一つ目|二つ目| 1| 2| 4| 1| ・・・略 test2には2つのフィールドがあり、1,2,3,4を既定値として 入れるようにしています。 やりたいとこは、test2をグループ化し「一つ目」と「二つ目」 の規定値の個数（1、2、3、4）それぞれのカウントを求めたいのです。 一つのフィールドの場合は、 select * from test1 join test2 test1.番号=test2.一つ目 group by test1.番号 で求められたのですが、二つのフィールドをグループ化した際、 一つ目のフィールドと同じ個数が二つ目のフィールドにコピーされてしまうのです。 ですので、FROM句でサブクエリを結合しその中で、 group化したものを、いくつかのサブクエリと結合し てみたのですが、うまくいきませんでした。 select cnt1.一つ目,cnt2.二つ目 from (select * from test1 left join test2 on test1.番号=test2.一つ目group by test1.番号) as cnt1 left join (select * from test1 left join test2 on test1.番号=test2.二つ目group by test1.番号) as cnt2 on cnt1.番号=cnt2.番号 といった形で作ってみました。 エイリアスをつけてしまった時点でエラーが出ているので、 どうにもしようがありません。 申し訳ないのですが、ご教授のほどよろしくお願いいたします。

  • 2テーブルが同じユーザテーブル参照するときの結合

    2つのテーブルで、同じユーザテーブルを参照してデータ取得したいのですが、 どうやって結合すれば良いのでしょうか？ ■概要 ・ユーザがアクションに挑戦すると、結果を表示 ・その際、「アクション作成者」と「アクション挑戦者(ユーザ)」を表示したい ※「作成者」「挑戦者」は、同じ「user」テーブルで管理したい ■3テーブル ・「result」「action」「user」 ・「result」「action」テーブルには、それぞれ「user_id」カラムがある ・「result」テーブルには、「action_id」カラムがある。1対多？ ■知りたいこと ・一気にまとめて一覧として取得する方法 ・最終的に「結果」と、「result」「action」それぞれに対応した「user」テーブルの「name」カラムを取得したい ■試してみたこと ・これだと「挑戦者名」が取得できない 　　　　　　SELECT * 　　　　　　　FROM result r 　　　　　　　　LEFT JOIN `action` a ON r.`action_id` = a.`id` 　　　　　　　　LEFT JOIN `user` u ON r.`user_id` = u.`id` ・これだと「挑戦者」のユーザIDしか取得できない 　　　　　　SELECT *,a.user_id AS actionuser_id 　　　　　　　FROM result r 　　　　　　　　LEFT JOIN `action` a ON r.`action_id` = a.`id` 　　　　　　　　LEFT JOIN `user` u ON a.`user_id` = u.`id`

    • ベストアンサー
    • MySQL
  • 少し複雑な　left join

    まだデーターベースを独学で始めて数ヶ月の初心者です。 以下（３行目のleft join）の記述では動かないのですが、どのように 記述したらよいのでしょうか？ やりたい内容は３行目にあるようにbookedというテーブルの中のdateが 2013-06-05だけ抽出した形のテーブルを、timeTplというテーブルとleft join させたいです。 1 "select * 2 from 3 timeTpl as t left join (SELECT * FROM booked where date ="2013-06-05") as b 4 on 5 t.start >= b.startTime and t.start < b.finishTime || 6 t.finish > b.startTime and t.finish <= b.finishTime 7 where 8 t.time > 9 (select start 10 from class as c left join member as m 11 on c.className = m.class 12 where m.name = '$name') 13 ) 14 "; よろしくお願いします。

  • 複数JOINしているとCOUNTが正しく取得できな

    LAMP環境で開発をしています。 SQL文でCOUNTを求める際に、まとめて結果を求めようとして上手く行きません。 状況としては以下です。 テーブルdはidをkeyにa,b,c３つのテーブルとjoinしています。 id = 1の場合、テーブルa,b,cにマッチするレコードがそれぞれに４個、１個、０個あります。 ひとつひとつを SELECT COUNT(CASE WHEN a.name IS null THEN 1 ELSE null END) as a_count FROM d INNER JOIN a ON a.id = d.id WHERE d.id = 1 として結果を求めると4,1,0と出るのですが、まとめて SELECT COUNT(CASE WHEN a.name IS null THEN 1 ELSE null END) as a_count, COUNT(CASE WHEN b.name IS null THEN 1 ELSE null END) as b_count, COUNT(CASE WHEN c.name IS null THEN 1 ELSE null END) as c_count FROM d INNER JOIN a ON a.id = d.id INNER JOIN b ON b.id = d.id INNER JOIN c ON c.id = d.id WHERE d.id = 1 とすると28,5,0という値が返されます。 どのように書けば正しい4,1,0を得られるのでしょうか？ よろしくお願いします。

    • ベストアンサー
    • MySQL
  • LEFT JOINの使い方

    linkテーブルとcommentテーブルがあります。 下記のようなSQLをPHPから発行すると、 select c.id AS cid, c.pid AS cpid, c.name AS cname, l.id AS id, l.url AS url from comment AS c LEFT JOIN link AS l ON c.pid=l.id order by c.id desc LIMIT 0, 10; $rec['cid'] や $rec['cname'] にはデータがあるのですが $rec['id'] や $rec['url'] にはデータが入っておらずNULLになってしまいます。 ためしに LEFT JOINの左右を下記のように入れ替えみたところ、 select c.id AS cid, c.pid AS cpid, c.name AS cname, l.id AS id, l.url AS url from link AS l JOIN comment AS c LEFT ON c.pid=l.id order by c.id desc LIMIT 0, 10; 今度は $rec['id'] や $rec['url'] にはデータがあるのですが $rec['cid'] や $rec['cname'] にはデータが入っておらずNULLになってしまいました。 どこがおかしいのでしょうか？ MYSQLは5.1.22-rcでレンタルサーバーはcoreserverを利用しています。

    • ベストアンサー
    • MySQL
  • サブクエリを使わずに実現したのですが。。。

    現在、Mysql 4.0xを利用してDBを構築しています。 しかしながら、サブクエリが使えないため以下のようなクエリが実現できず困っております。 EX: select mg.*,sumlg.* from mg left join (select id,uid from lg where uid = '00001') as sumlg on mg.id = sumlg.id where sumlg.id is null; 上記SQLは特定のユーザーIDの、テーブルBに存在しないテーブルAのIDをユーザー単位で切り分け抽出するSQL文です。 （テーブルBにはIDとUIDでユニークになるレコードが格納されています。） 上記を実現するためのSQLをどなたかアドバイスください。 どうぞよろしくお願いします。

    • ベストアンサー
    • MySQL
  • サブクエリの複数項目

    いつもお世話になっております。 サブクエリで複数項目を取得する方法をご教授下さい。 [既存] select 商品, 価格, (select user_id from user_master where user_id = 1 ) from products where 商品ID = 1 上記のようなSQLがあり、 user_masterから取得する項目をもうひとつ追加したいと思っています。(user_name) select 商品, 価格, (select user_id, user_name from user_master where user_id = 1 ) as user from products where 商品ID = 1 と修正したいのですが、当然エラーになってしまいます。 この場合は別にサブクエリを指定して取得した方がいいのでしょうか。 宜しくお願い致します。

  • MySQLでLEFT JOIN

    MySQL4.0.26でテーブルの左外部結合がしたいです。 左テーブルAと 右テーブルBをWHERE句で抽出したもの を結合することはできないのでしょうか？ 考えた以下の式ではエラーが出てしまいます。 SELECT * FROM A LEFT JOIN ( SELECT * B WHERE id = 1 ) AS B2 ON A.id = B2.id

    • ベストアンサー
    • MySQL

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する