階層構造のテーブルの行の取得について
このジャンルでお願いします。
次のようなテーブルで
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文にすれば良いのでしょうか？
お礼
pon-cyanさん、ありがとうございます 一番気になってるのが２）なのですが、pon-cyanさんのところにあるのは変身タイプだから付け替えられるのかなー、、という感じもしますね 部屋の隅においてもけっこう邪魔な感じっていうの、参考になります！