まずここをチェック！を先に
「フィールド名=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';
