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

公開日:2014年09月20日

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)

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

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';

コンテンツ一覧

同じカテゴリーのコンテンツ