SET FOREIGN_KEY_CHECKS=0; DROP DATABASE IF EXISTS test; CREATE DATABASE test; USE test; CREATE TABLE `td` ( `id` varchar(255) NOT NULL DEFAULT '', `familyid` int(11) DEFAULT NULL, `withdrawndate` date DEFAULT NULL, PRIMARY KEY (`id`), KEY `index_td_familyid_id` (`familyid`,`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `fd` ( `id` int(11) NOT NULL AUTO_INCREMENT, `activefromts` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `shortdescription` text, `useraccessfamily` varchar(512) DEFAULT NULL, `serialized` longtext, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=163 DEFAULT CHARSET=utf8; CREATE TABLE `fs` ( `id` int(11) NOT NULL DEFAULT '0', `classnode` varchar(200) NOT NULL DEFAULT '', KEY `fs_foreignkey_1` (`id`), CONSTRAINT `fs_foreignkey_1` FOREIGN KEY (`id`) REFERENCES `fd` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; DROP TABLE IF EXISTS `fd`; CREATE TABLE `fd` ( `id` int(11) NOT NULL AUTO_INCREMENT, `activefromts` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `shortdescription` text, `useraccessfamily` varchar(512) DEFAULT NULL, `serialized` longtext, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=163 DEFAULT CHARSET=utf8; insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (38,'2013-03-04 07:49:22','Max Mustermann','BA008609','/root/documentinfo/picturesubuser\n 001\n '); insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (39,'2013-03-04 07:50:08','Max Mustermann','BA008600','/root/documentinfo/picturesubuser\n 001\n '); insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (51,'2013-03-13 16:48:55','Max Muster','BA010300','/root/documentinfo/picturesubuser\n 001\n '); insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (58,'2013-03-14 17:26:32','Max Power','BA013400','/root/documentinfo/picturesubuser\n 002\n '); insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (66,'2013-03-26 15:24:11','Unterschrift Max M.','BA013400','/root/documentinfo/picturesubuser\n 001\n '); insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (74,'2013-04-04 14:08:16','Hof Bauer Außenansicht','ZIPCON',''); insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (88,'2013-05-27 15:40:25','Klaus Kiefer','BA014300','/root/documentinfo/picturesubuser\n 001\n '); insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (89,'2013-05-28 08:20:39','Ralph Hanwalter','BA003909','/root/documentinfo/picturesubuser\n 001\n '); insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (90,'2013-05-29 14:54:26','Unterschrift Max Mustermann','BA012709','/root/documentinfo/picturesubuser\n 001\n '); insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (111,'2013-06-26 09:57:22','Klaus Kiefer','BA014309','/root/documentinfo/picturesubuser\n 001\n '); insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (140,'2013-08-14 16:28:58','bungalow78-trend','BA014300',''); insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (142,'2013-08-14 16:32:36','demohaus2','BA014300',''); insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (143,'2013-08-14 16:32:15','doppelhaus-behringen116-trend','BA014300',''); insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (144,'2013-08-14 16:31:45','landhaus142-trend','BA014300',''); insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (146,'2013-08-14 16:31:09','bungalow78-trend','BA014300',''); insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (147,'2013-08-14 16:35:51','demohaus1','BA014300',''); insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (148,'2013-08-14 16:37:38','demohaus2','BA014300',''); insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (149,'2013-08-14 16:39:05','doppelhaus-behringen116-trend','BA014300',''); insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (150,'2013-08-14 16:40:40','landhaus142-trend','BA014300',''); insert into `fd`(`id`,`activefromts`,`shortdescription`,`useraccessfamily`,`serialized`) values (162,'2013-08-30 09:47:01','Max Muster','BA010309','/root/documentinfo/picturesubuser\n 001\n '); DROP TABLE IF EXISTS `fs`; CREATE TABLE `fs` ( `id` int(11) NOT NULL DEFAULT '0', `classnode` varchar(200) NOT NULL DEFAULT '', KEY `fs_foreignkey_1` (`id`), CONSTRAINT `fs_foreignkey_1` FOREIGN KEY (`id`) REFERENCES `fd` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into `fs`(`id`,`classnode`) values (74,'/tree=userpicture/kategorie=aussenaufnahmen/'); insert into `fs`(`id`,`classnode`) values (74,'/tree=userpicture/kategorie=sonstiges/'); insert into `fs`(`id`,`classnode`) values (143,'/tree=userpicture/kategorie=aussenaufnahmen/'); insert into `fs`(`id`,`classnode`) values (149,'/tree=userpicture/kategorie=sonstiges/'); insert into `fs`(`id`,`classnode`) values (143,'/tree=userpicture/kategorie=sonstiges/'); insert into `fs`(`id`,`classnode`) values (149,'/tree=userpicture/kategorie=aussenaufnahmen/'); insert into `fs`(`id`,`classnode`) values (150,'/tree=userpicture/kategorie=sonstiges/'); insert into `fs`(`id`,`classnode`) values (144,'/tree=userpicture/kategorie=sonstiges/'); insert into `fs`(`id`,`classnode`) values (150,'/tree=userpicture/kategorie=aussenaufnahmen/'); insert into `fs`(`id`,`classnode`) values (144,'/tree=userpicture/kategorie=aussenaufnahmen/'); insert into `fs`(`id`,`classnode`) values (66,'/tree=userpicture/kategorie=unterschrift/'); insert into `fs`(`id`,`classnode`) values (58,'/tree=userpicture/kategorie=unterschrift/'); insert into `fs`(`id`,`classnode`) values (88,'/tree=userpicture/kategorie=unterschrift/'); insert into `fs`(`id`,`classnode`) values (162,'/tree=userpicture/kategorie=unterschrift/'); insert into `fs`(`id`,`classnode`) values (51,'/tree=userpicture/kategorie=unterschrift/'); insert into `fs`(`id`,`classnode`) values (90,'/tree=userpicture/kategorie=unterschrift/'); insert into `fs`(`id`,`classnode`) values (89,'/tree=userpicture/kategorie=unterschrift/'); insert into `fs`(`id`,`classnode`) values (38,'/tree=userpicture/kategorie=unterschrift/'); insert into `fs`(`id`,`classnode`) values (39,'/tree=userpicture/kategorie=unterschrift/'); insert into `fs`(`id`,`classnode`) values (111,'/tree=userpicture/kategorie=unterschrift/'); DROP TABLE IF EXISTS `td`; CREATE TABLE `td` ( `id` varchar(255) NOT NULL DEFAULT '', `familyid` int(11) DEFAULT NULL, `withdrawndate` date DEFAULT NULL, PRIMARY KEY (`id`), KEY `index_td_familyid_id` (`familyid`,`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/111/1372233400642.pmd',111,NULL); insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/140/1376487699812.pmd',140,'2013-08-14'); insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/140/1376487727471.pmd',140,'2013-08-14'); insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/142/1376487973857.pmd',142,'2013-08-14'); insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/142/1376488019348.pmd',142,'2013-08-14'); insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/143/1376488153694.pmd',143,'2013-08-14'); insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/143/1376488175601.pmd',143,'2013-08-14'); insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/144/1376488236640.pmd',144,'2013-08-14'); insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/144/1376488329609.pmd',144,'2013-08-14'); insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/146/1376490601869.pmd',146,NULL); insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/146/1376490664368.pmd',146,NULL); insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/147/1376490913146.pmd',147,NULL); insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/147/1376490946241.pmd',147,NULL); insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/148/1376491009680.pmd',148,NULL); insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/148/1376491054867.pmd',148,NULL); insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/149/1376491116794.pmd',149,NULL); insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/149/1376491141665.pmd',149,NULL); insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/150/1376491203736.pmd',150,NULL); insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/150/1376491236675.pmd',150,NULL); insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/162/1377848818511.pmd',162,NULL); insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/38/1362379759464.pmd',38,NULL); insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/39/1362379804507.pmd',39,NULL); insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/51/1363189729391.pmd',51,NULL); insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/58/1363277790564.pmd',58,NULL); insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/66/1364307582985.pmd',66,NULL); insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/74/1364916039095.pmd',74,NULL); insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/88/1369662014970.pmd',88,NULL); insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/89/1369722032695.pmd',89,NULL); insert into `td`(`id`,`familyid`,`withdrawndate`) values ('picture/90/1369832057370.pmd',90,NULL); SELECT DISTINCT x.useraccessfamily, x.picturesubuser, x.familyid, x.shortdescription, x.activefromts AS `letzte Änderung am` FROM ( SELECT fd.id AS familyid, fd.shortdescription, fd.useraccessfamily, IF(INSTR(fd.serialized, '/root/documentinfo/picturesubuser') > 0, SUBSTRING_INDEX( SUBSTRING_INDEX( SUBSTRING_INDEX( fd.serialized, '/root/documentinfo/picturesubuser', -1 ), '', 1 ), '', -1 ), '') AS picturesubuser, fd.activefromts FROM fd, fs WHERE fd.id = fs.id AND ( fs.classnode LIKE '/tree=userpicture/level1=unterschriften/%' OR fs.classnode LIKE '/tree=userpicture/kategorie=unterschrift/%' ) AND IF(INSTR(fd.serialized, '/root/documentinfo/picturesubuser') > 0, SUBSTRING_INDEX( SUBSTRING_INDEX( SUBSTRING_INDEX( fd.serialized, '/root/documentinfo/picturesubuser', -1 ), '', 1 ), '', -1 ), '') <> '' ) `x` , ( SELECT fd.useraccessfamily, IF(INSTR(fd.serialized, '/root/documentinfo/picturesubuser') > 0, SUBSTRING_INDEX( SUBSTRING_INDEX( SUBSTRING_INDEX( fd.serialized, '/root/documentinfo/picturesubuser', -1 ), '', 1 ), '', -1 ), '') AS picturesubuser, COUNT(*) FROM fd, td WHERE td.familyid = fd.id AND td.withdrawndate IS NULL AND IF(INSTR(fd.serialized, '/root/documentinfo/picturesubuser') > 0, SUBSTRING_INDEX( SUBSTRING_INDEX( SUBSTRING_INDEX( fd.serialized, '/root/documentinfo/picturesubuser', -1 ), '', 1 ), '', -1 ), '') <> '' GROUP BY fd.useraccessfamily, IF(INSTR(fd.serialized, '/root/documentinfo/picturesubuser') > 0, SUBSTRING_INDEX( SUBSTRING_INDEX( SUBSTRING_INDEX( fd.serialized, '/root/documentinfo/picturesubuser', -1 ), '', 1 ), '', -1 ), '') HAVING COUNT(*) > 1 ) `y` WHERE x.useraccessfamily = y.useraccessfamily AND x.picturesubuser = y.picturesubuser ORDER BY 1, 2, 3;