階層構造のテーブルの行の取得について
このジャンルでお願いします。
次のようなテーブルで
CREATE TABLE IF NOT EXISTS user (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(32) NOT NULL,
PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO user (name) VALUES ('user1');
INSERT INTO user (name) VALUES ('user2');
INSERT INTO user (name) VALUES ('user3');
CREATE TABLE IF NOT EXISTS item (
id int(11) NOT NULL AUTO_INCREMENT,
parent_id INT,
name varchar(32) NOT NULL,
FOREIGN KEY (parent_id) REFERENCES item(id) ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO item (parent_id, name) VALUES (null, 'item1');
INSERT INTO item (parent_id, name) VALUES (1, 'item2');
INSERT INTO item (parent_id, name) VALUES (2, 'item3');
INSERT INTO item (parent_id, name) VALUES (3, 'item4');
INSERT INTO item (parent_id, name) VALUES (2, 'item5');
INSERT INTO item (parent_id, name) VALUES (null, 'item6');
INSERT INTO item (parent_id, name) VALUES (6, 'item7');
INSERT INTO item (parent_id, name) VALUES (null, 'item8');
CREATE TABLE IF NOT EXISTS user_item (
id int(11) NOT NULL AUTO_INCREMENT,
user_id INT,
item_id INT,
type varchar(16) NOT NULL,
PRIMARY KEY(id),
FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (item_id) REFERENCES item(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO user_item (user_id, item_id, type) VALUES (1, 1, 'allow');
INSERT INTO user_item (user_id, item_id, type) VALUES (1, 3, 'deny');
INSERT INTO user_item (user_id, item_id, type) VALUES (1, 7, 'allow');
user_itemのuser_idが1のitemの行を取得したいのですが、
その条件として
・user_itemテーブルのtypeが'allow'のitem_id以下のitemの行
・ただしtypeが'deny'のitem_id以下の行は除く
この例だと、user_itemの
INSERT INTO user_item (user_id, item_id, type) VALUES (1, 1, 'allow');
の行によってitemテーブルの
INSERT INTO item (parent_id, name) VALUES (null, 'item1');
INSERT INTO item (parent_id, name) VALUES (1, 'item2');
INSERT INTO item (parent_id, name) VALUES (2, 'item3');
INSERT INTO item (parent_id, name) VALUES (3, 'item4');
INSERT INTO item (parent_id, name) VALUES (2, 'item5');
が取得候補になりますが、
INSERT INTO user_item (user_id, item_id, type) VALUES (1, 3, 'deny');
によって
INSERT INTO item (parent_id, name) VALUES (2, 'item3');
INSERT INTO item (parent_id, name) VALUES (3, 'item4');
の行は除かれ、
INSERT INTO item (parent_id, name) VALUES (null, 'item1');
INSERT INTO item (parent_id, name) VALUES (1, 'item2');
INSERT INTO item (parent_id, name) VALUES (2, 'item5');
の行が取得されます。
つまりitemテーブルにおいて上の階層の直近の（user_itemと結合して取得した）typeが
'allow'である場合のみ取得したいのです。
INSERT INTO item (parent_id, name) VALUES (null, 'item1');←取得
INSERT INTO item (parent_id, name) VALUES (1, 'item2');←取得
INSERT INTO item (parent_id, name) VALUES (2, 'item3');
INSERT INTO item (parent_id, name) VALUES (3, 'item4');
INSERT INTO item (parent_id, name) VALUES (2, 'item5');←取得
INSERT INTO item (parent_id, name) VALUES (null, 'item6');
INSERT INTO item (parent_id, name) VALUES (6, 'item7');←取得
INSERT INTO item (parent_id, name) VALUES (null, 'item8');
複雑ではありますが、これはどのようなSQL文にすれば良いのでしょうか？
お礼
naktak様。こんにちは。 ご指導ありがとうございました。 >どこを指しているのかわかりません。 すみません。最初に記述した投降のテーブル内容に 記述ミスをしておりました。 テーブル構造を書き直しました。 【テーブル構造】 mysql> select * from name; +------+------+------+------+------------+--------+------+ | nkey | name | age | sei | tou | fuken | kahi | +------+------+------+------+------------+--------+------+ | 1 | 田中 | 17 | 2 | 2005/12/25 | 大阪府 | 可 | | 2 | 佐藤 | 20 | 1 | 2006/02/25 | 京都府 | 可 | | 3 | 鈴木 | 25 | 2 | 2006/04/25 | 北海道 | 可 | | 4 | 山田 | 30 | 1 | 2006/03/25 | 東京都 | 可 | | 5 | 阿部 | 19 | 2 | 2005/11/25 | 大阪府 | 可 | +------+------+------+------+------------+--------+------+ 5 rows in set (0.00 sec) mysql> select * from jyan; +---------+----------+ | jyankey | jyanname | +---------+----------+ | 1 | 顔型 | | 2 | 髪型 | | 3 | 上着 | | 4 | 下着 | | 5 | 靴 | | 6 | アイテム | +---------+----------+ 6 rows in set (0.00 sec) mysql> select * from soubi; +--------+------+---------+--------+ | soukey | nkey | jyankey | jyanno | +--------+------+---------+--------+ | 1 | 1 | 1 | 10 | | 2 | 1 | 2 | 3 | | 3 | 1 | 3 | 2 | | 4 | 1 | 4 | 8 | | 5 | 2 | 1 | 10 | | 6 | 2 | 5 | 10 | | 7 | 2 | 2 | 8 | | 8 | 2 | 3 | 7 | | 9 | 2 | 4 | 8 | | 10 | 2 | 6 | 1 | | 11 | 4 | 1 | 10 | | 12 | 4 | 2 | 10 | | 13 | 4 | 3 | 8 | | 14 | 4 | 4 | 7 | | 15 | 4 | 5 | 8 | | 16 | 4 | 6 | 1 | +--------+------+---------+--------+ 16 rows in set (0.00 sec)
補足
文字数が多いので、途中で切れてしまいました……。 つづきです。 上記テーブルを、PHPからHTMLで吐き出した時に下記の 表にしたいのです。 【出力テーブルイメージ】 名前┃可否┃登録日┃顔型┃髪型┃上着┃下着┃靴┃アイテム -------------------------------------------------- 田中┃可 ┃2006/04/01┃10┃2┃3┃4┃12┃NULL┃NULL┃ ------------------------------------------------- 佐藤┃否 ┃2005/01/12┃NULL┃1┃3┃4┃NULL┃NULL 何度も申し訳ありません。 ご指導よろしくお願い申し上げます。