階層構造のテーブルの行の取得について
このジャンルでお願いします。
次のようなテーブルで
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文にすれば良いのでしょうか？
お礼
ビスを外してもハンドルが取れなかったのでプライヤーが必要で質問してるんです。 アマゾンでどれを買えばいいのか具体的に商品のリンクを教えてくださいと尋ねてます。