Thursday, January 5, 2012

SQL trees filtration

This article solves remove-matching-children problem and avoid recursion.

DROP TABLE tree_element;
CREATE TABLE tree_element (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
pid INT NOT NULL,
description VARCHAR(100),
filter1 INT DEFAULT 0,
filter2 INT DEFAULT 0
);

DROP TABLE treelinks;
CREATE TABLE treelinks (
tid INT NOT NULL,
pid INT NOT NULL
);

INSERT INTO tree_element (id,pid,description,filter1,filter2) VALUES ( 1,0, "te1" , 0,0 );
INSERT INTO tree_element (id,pid,description,filter1,filter2) VALUES ( 2,1, "te1.1" , 0,0 );
INSERT INTO tree_element (id,pid,description,filter1,filter2) VALUES ( 3,2, "te1.1.1" , 1,0 );
INSERT INTO tree_element (id,pid,description,filter1,filter2) VALUES ( 4,2, "te1.1.2" , 0,0 );
INSERT INTO tree_element (id,pid,description,filter1,filter2) VALUES ( 5,1, "te1.2" , 0,0 );
INSERT INTO tree_element (id,pid,description,filter1,filter2) VALUES ( 6,5, "te1.2.1" , 0,0 );
INSERT INTO tree_element (id,pid,description,filter1,filter2) VALUES ( 7,5, "te1.2.2" , 1,0 );
INSERT INTO tree_element (id,pid,description,filter1,filter2) VALUES ( 8,7, "te1.2.2.1", 1,0 );
INSERT INTO tree_element (id,pid,description,filter1,filter2) VALUES ( 9,7, "te1.2.2.2", 1,0 );

INSERT INTO treelinks ( tid, pid ) VALUES ( 2, 1 );
INSERT INTO treelinks ( tid, pid ) VALUES ( 3, 1 );
INSERT INTO treelinks ( tid, pid ) VALUES ( 3, 2 );
INSERT INTO treelinks ( tid, pid ) VALUES ( 4, 1 );
INSERT INTO treelinks ( tid, pid ) VALUES ( 4, 2 );
INSERT INTO treelinks ( tid, pid ) VALUES ( 5, 1 );
INSERT INTO treelinks ( tid, pid ) VALUES ( 6, 1 );
INSERT INTO treelinks ( tid, pid ) VALUES ( 6, 5 );
INSERT INTO treelinks ( tid, pid ) VALUES ( 7, 1 );
INSERT INTO treelinks ( tid, pid ) VALUES ( 7, 5 );
INSERT INTO treelinks ( tid, pid ) VALUES ( 8, 1 );
INSERT INTO treelinks ( tid, pid ) VALUES ( 8, 5 );
INSERT INTO treelinks ( tid, pid ) VALUES ( 8, 7 );
INSERT INTO treelinks ( tid, pid ) VALUES ( 9, 1 );
INSERT INTO treelinks ( tid, pid ) VALUES ( 9, 5 );
INSERT INTO treelinks ( tid, pid ) VALUES ( 9, 7 );

SELECT * FROM tree_element AS t2
WHERE filter1 = 1 AND NOT EXISTS (
SELECT tid FROM tree_element AS t1
JOIN treelinks ON t1.id=treelinks.pid
WHERE t1.filter1 = 1 AND treelinks.tid = t2.id
);

No comments:

Post a Comment