Learning site for website creation

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

公開日:2014年09月20日

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

「フィールド名=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)

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

https://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';