JOIN先にNULLレコードを含んでひとつの表にしたい
JOIN先がNULLになる場合にどういう処理を行うのが最適なのか。
最適解を求めて悩み始めたので、ココはお悩みを晒してみる。
こんな感じのテーブル構成になっています。
やりたいことは、usersとattendsをくっつけて、attendsのevent_idをキーにこういう結果を返すこと。
user_id | username | status |
---|---|---|
1 | test | 参加 |
2 | test2 | 参加 |
3 | test3 | NULL |
単純にLEFT JOINしてもwhere句で絞り込んでしまうと「参加表明すらしてない人」=「Attendsにレコードが無い人」を取得出来ない・・・!
PostgesqlやOracleだとJOIN先のレコードが存在しなくてもFULL JOINでusersとattendsをもってくるだけの簡単なお仕事ですが、mysqlはサポートしていないらしい。
てっとりばやいのは、2発SQL投げてアプリ側で整形するというもの。CakePHPだとこんな感じ。
<?php //usersにいるユーザー一覧を取得 $userlist = $this-query('select * from users'); //attendsにいるユーザー一覧を取得 $attendants = $this->query('select * from attends where event_id = ?',$id); foreach($attendants as $v){ foreach($userlist as $u) { //参加表明してれば適当にstatusを作って代入 if($v['user_id'] == $u['user_id']) { $u['status'] = $v['status']; } } }
でもこれじゃあ単純バカすぎて不安。
一発でビシっと抜くか、php側でビシッと置換するかどっちかしたい。
皆さんはどう対応しているのやろうか。
追記(2010/12/10 23:15)
とりあえずこれで1発で抜けるようになった。
SELECT u.id, username, a.status FROM users AS u LEFT JOIN attends AS a ON u.id = a.user_id UNION SELECT u.id, username, a.status FROM users AS u RIGHT JOIN attends AS a ON u.id = a.user_id WHERE event_id =?
LEFT JOINとRIGHT JOINの結果をUNIONすれば全部抜けるだろっていう荒業。これで上記の様な結果が返ってくる。