GROUP BY と LEFT JOIN を使って表示されないレコードの対策

公開日: : 最終更新日:2014/10/25 MySQL

まずここをチェック!を先に

「フィールド名=NULL」はダメ 「フィールド名 IS NULL」を使う

やりたいこと

  • 会員を全員の名前を表示したい
  • 最新の予約日付を表示したい

使用するテーブル

会員テーブル

mysql> SELECT * FROM member;
+----+--------+
| id | name   |
+----+--------+
|  1 | タナカ |
|  2 | スズキ |
|  3 | サトウ |
+----+--------+
3 rows in set (0.00 sec)

id:会員番号(リレーションに使用)
name:名前

 予約テーブル

mysql> SELECT * FROM reserve;
+------+------+----------+------------+
| r_id | m_id | r_cancel | r_date     |
+------+------+----------+------------+
|    1 |    1 |        1 | 2014-09-01 |
|    2 |    1 |        1 | 2014-09-09 |
|    3 |    1 |        0 | 2014-09-05 |
|    4 |    2 |        0 | 2014-09-02 |
+------+------+----------+------------+
4 rows in set (0.00 sec)

r_id:予約番号
m_id:会員番号(リレーションに使用)
r_cancel:キャンセル(0:有効 1:キャンセル済)
r_date:予約日

リレーションする

JOIN を使ってmember テーブルと reserve テーブルをリレーション。

リレーションフィールドは「member.id=reserve.m_id」。

mysql> SELECT * FROM member JOIN reserve ON member.id=reserve.m_id;
+----+--------+------+------+----------+------------+
| id | name   | r_id | m_id | r_cancel | r_date     |
+----+--------+------+------+----------+------------+
|  1 | タナカ |    1 |    1 |        1 | 2014-09-01 |
|  1 | タナカ |    2 |    1 |        1 | 2014-09-09 |
|  1 | タナカ |    3 |    1 |        0 | 2014-09-05 |
|  2 | スズキ |    4 |    2 |        0 | 2014-09-02 |
+----+--------+------+------+----------+------------+
4 rows in set (0.00 sec)

予約を入れていない「サトウ」が表示されていない。

JOIN を LEFT JOIN に変更して左の member テーブルをすべて表示する。

mysql> SELECT * FROM member LEFT JOIN reserve ON member.id=reserve.m_id;
+----+--------+------+------+----------+------------+
| id | name   | r_id | m_id | r_cancel | r_date     |
+----+--------+------+------+----------+------------+
|  1 | タナカ |    1 |    1 |        1 | 2014-09-01 |
|  1 | タナカ |    2 |    1 |        1 | 2014-09-09 |
|  1 | タナカ |    3 |    1 |        0 | 2014-09-05 |
|  2 | スズキ |    4 |    2 |        0 | 2014-09-02 |
|  3 | サトウ | NULL | NULL |     NULL | NULL       |
+----+--------+------+------+----------+------------+
5 rows in set (0.00 sec)

予約を入れていない「サトウ」が表示された。

「タナカ」がたくさん表示されているので、「GROUP BY id」 を使って重複するID番号「1」の「タナカ」をまとめる。

mysql> SELECT * FROM member LEFT JOIN reserve ON member.id=reserve.m_id GROUP BY id;
+----+--------+------+------+----------+------------+
| id | name   | r_id | m_id | r_cancel | r_date     |
+----+--------+------+------+----------+------------+
|  1 | タナカ |    1 |    1 |        1 | 2014-09-01 |
|  2 | スズキ |    4 |    2 |        0 | 2014-09-02 |
|  3 | サトウ | NULL | NULL |     NULL | NULL       |
+----+--------+------+------+----------+------------+
3 rows in set (0.00 sec)

キャンセルした予約(r_cancel が「1」)が表示されている。

有効な予約(r_cancel が「0」)のみ取得する条件「WHERE r_cancel=0」をつける。

WHERE は GROUP BY の前に記述するので注意。

mysql> SELECT * FROM member LEFT JOIN reserve ON member.id=reserve.m_id WHERE r_cancel=0 GROUP BY id;
+----+--------+------+------+----------+------------+
| id | name   | r_id | m_id | r_cancel | r_date     |
+----+--------+------+------+----------+------------+
|  1 | タナカ |    3 |    1 |        0 | 2014-09-05 |
|  2 | スズキ |    4 |    2 |        0 | 2014-09-02 |
+----+--------+------+------+----------+------------+
2 rows in set (0.00 sec)

「サトウ」が消える。 「サトウ」の r_cancel は「NULL」なのではじかれた。

「r_cancel=NULL」を条件に追加する。

mysql> SELECT * FROM member LEFT JOIN reserve ON member.id=reserve.m_id WHERE r_cancel=0 OR r_cancel=NULL GROUP BY id;
+----+--------+------+------+----------+------------+
| id | name   | r_id | m_id | r_cancel | r_date     |
+----+--------+------+------+----------+------------+
|  1 | タナカ |    3 |    1 |        0 | 2014-09-05 |
|  2 | スズキ |    4 |    2 |        0 | 2014-09-02 |
+----+--------+------+------+----------+------------+
2 rows in set (0.00 sec)

「サトウ」表示されない。マニュアルを見てみる。

http://dev.mysql.com/doc/refman/5.7/en/problems-with-null.html

「フィールド名=NULL」は使えないことがわかる。使えると勘違いしてた・・・。

「r_cancel=NULL」を「r_cancel IS NULL」に変更。

条件は「WHERE r_cancel=0 OR r_cancel IS NULL」になる。

mysql> SELECT * FROM member LEFT JOIN reserve ON member.id=reserve.m_id WHERE r_cancel=0 OR r_cancel IS NULL GROUP BY id;
+----+--------+------+------+----------+------------+
| id | name   | r_id | m_id | r_cancel | r_date     |
+----+--------+------+------+----------+------------+
|  1 | タナカ |    3 |    1 |        0 | 2014-09-05 |
|  2 | スズキ |    4 |    2 |        0 | 2014-09-02 |
|  3 | サトウ | NULL | NULL |     NULL | NULL       |
+----+--------+------+------+----------+------------+
3 rows in set (0.00 sec)

会員全員の名前が表示された。最新の有効な予約日時(r_dateフィールド)も表示されている。

「フィールド名=NULL」はダメ 「フィールド名 IS NULL」を使う

今回の検証用スクリプトファイル:jobtechデータベース領域が作成されます

#「jobtech」データベース領域削除
DROP DATABASE IF EXISTS jobtech;

#「jobtech」データベース領域作成
CREATE DATABASE jobtech;

#「jobtech」データベース領域選択
USE jobtech

#「member」テーブル作成
CREATE TABLE member (
id INT PRIMARY KEY AUTO_INCREMENT,
name CHAR(15) NOT NULL
);

#「member」テーブルに会員を追加
INSERT INTO member SET name='タナカ';
INSERT INTO member SET name='スズキ';
INSERT INTO member SET name='サトウ';

#「reserve」テーブル作成
create table reserve(
r_id INT PRIMARY KEY AUTO_INCREMENT,
m_id INT NOT NULL,
r_cancel INT NOT NULL default 0,
r_date DATE NOT NULL
);

#reserveに予約情報を追加
INSERT INTO reserve SET m_id=1, r_cancel=1, r_date='2014-9-1';
INSERT INTO reserve SET m_id=1, r_cancel=1, r_date='2014-9-9';
INSERT INTO reserve SET m_id=1, r_cancel=0, r_date='2014-9-5';
INSERT INTO reserve SET m_id=2, r_cancel=0, r_date='2014-9-2';

関連記事

mysql

複数レコードを一度に登録

1レコードずつ登録 毎度フィールド名を入れるのが面倒。 INSERT INTO test ( フィールド名1, フィールド名2, フィールド名3 ) VALUES ( 値1, 値2, 値3 ); ...

記事を読む

wp
WordPressフロントページ設定とテンプレート

固定ページに最新投稿一覧を表示させた時の使用テンプレートが何なのか調べる機会があったので保存。 管理画面「設定」→「表示設定」の「フロントページの表示」 「最新の投稿」選択時 フロントページ...

js
GoogleMap左上の白い枠を消す

左上の白い枠を消してほしいという要望が多いので。よく使うマップコードをスクラップ。 <!doctype html> <html> <head>...

wp
WordPressでよく使う関数

string get_site_url( ] ] ) 管理者ページの「設定」-「一般」の「WordPressのアドレス(URL)」 現在のブログのサイトURL取得 [php]<?php ...

wp
WordPressにFacebookのいいねボタン

WordPressにFacebookのいいねボタンを表示する。 コピペで対応したい時用 <body>の直後に記述するコード <div id="fb-root&qu...

js
スムーススクロールを作る – 応用

指定位置にするするとスクロールするプログラムを作ります。 必要な値は? ページ内スクロールをした際のゴール地点「y座標」の値 画面に必要なパーツは? 高さのあるコン...

→もっと見る

    • icon_sublime1
      41,217 views

      ファイルの操作 ファイル名を検索して開く Windows:Ctrl+P Mac:Cmd+P 表示された入力欄にファイル名の一部を入力すると検索結果が表示されるので選択して開きます。 使用言語の指定 Windows:Ctrl+Shift+P Mac:Cmd+Shift+P 表示された入力欄に使用する言語名の一部を入力すると検索結果に「Set Syntax」が表示されるので選択しま...

    • 曜日番号
      17,999 views

      現在の曜日番号を表示 [php]<?php //現在の曜日番号(日:0 月:1 火:2 水:3 木:4 金:5 土:6)を取得 $weekno = date('w'); //現在の曜日番号出力 echo $weekno; ?>[/php] 現在の曜日を表示 [php]<?php //日本語の曜日配列 $weekjp = array( '日', //0 ...

    • wp
      8,630 views

      WordPressの固定ページに投稿ページの最新記事を表示する。 [php]<?php //最新5件を表示 $paged = get_query_var('paged'); //現在のページ番号 $num = 5; //表示件数 query_posts('posts_per_page='.$num.'&paged='.$paged); if ( have_posts() ) :...

    • groupby
      6,344 views

      まずここをチェック!を先に 「フィールド名=NULL」はダメ 「フィールド名 IS NULL」を使う やりたいこと 会員を全員の名前を表示したい 最新の予約日付を表示したい 使用するテーブル 会員テーブル [ps]mysql> SELECT * FROM member; +----+--------+ | id | name | +----+----...

    • icon_kaiten
      5,788 views

      jQueryのanimateメソッドを使って要素を回転させてみるプログラム。 サンプルデータ [js] $('#box1').animate( {'z-index': 1},//z-indexを0から1に変更する { duration: 1000, //アニメーションの時間 //ステップ中の処理 //引数num:処理途中の変化している値 step: f...

PAGE TOP ↑
←幅を狭くする