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;
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, '<string>/root/documentinfo/picturesubuser</string>') > 0,
    SUBSTRING_INDEX(
                    SUBSTRING_INDEX(
                                    SUBSTRING_INDEX(
                                                    fd.serialized,
                                                    '<string>/root/documentinfo/picturesubuser</string>',
                                                    -1
                                                   ),
                                                 '</string>',
                                    1
                                   ),
                    '<string>',
                    -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, '<string>/root/documentinfo/picturesubuser</string>') > 0,
    SUBSTRING_INDEX(
                    SUBSTRING_INDEX(
                                    SUBSTRING_INDEX(
                                                    fd.serialized,
                                                    '<string>/root/documentinfo/picturesubuser</string>',
                                                    -1
                                                   ),
                                                 '</string>',
                                    1
                                   ),
                    '<string>',
                    -1
                   ),
    '') <> ''
) `x`
,
(
SELECT
  fd.useraccessfamily,
  IF(INSTR(fd.serialized, '<string>/root/documentinfo/picturesubuser</string>') > 0,
    SUBSTRING_INDEX(
                    SUBSTRING_INDEX(
                                    SUBSTRING_INDEX(
                                                    fd.serialized,
                                                    '<string>/root/documentinfo/picturesubuser</string>',
                                                    -1
                                                   ),
                                                 '</string>',
                                    1
                                   ),
                    '<string>',
                    -1
                   ),
    '') AS picturesubuser,
  COUNT(*)
FROM
  fd,
  td
WHERE
  td.familyid = fd.id AND
  td.withdrawndate IS NULL AND
IF(INSTR(fd.serialized, '<string>/root/documentinfo/picturesubuser</string>') > 0,
    SUBSTRING_INDEX(
                    SUBSTRING_INDEX(
                                    SUBSTRING_INDEX(
                                                    fd.serialized,
                                                    '<string>/root/documentinfo/picturesubuser</string>',
                                                    -1
                                                   ),
                                                 '</string>',
                                    1
                                   ),
                    '<string>',
                    -1
                   ),
    '') <> ''
GROUP BY
  fd.useraccessfamily,
  IF(INSTR(fd.serialized, '<string>/root/documentinfo/picturesubuser</string>') > 0,
    SUBSTRING_INDEX(
                    SUBSTRING_INDEX(
                                    SUBSTRING_INDEX(
                                                    fd.serialized,
                                                    '<string>/root/documentinfo/picturesubuser</string>',
                                                    -1
                                                   ),
                                                 '</string>',
                                    1
                                   ),
                    '<string>',
                    -1
                   ),
    '')

HAVING COUNT(*) > 1
) `y`
WHERE
  x.useraccessfamily = y.useraccessfamily AND
  x.picturesubuser = y.picturesubuser
ORDER BY
  1, 2, 3;
