SELECT *
  FROM person p
  INNER JOIN personzukat z
    ON z.zuperson=p.ident
  INNER JOIN kategorie k
    ON k.ident=z.zukat
  WHERE LOWER(k.kat) LIKE '%eins%';

SELECT *
  FROM person p
  INNER JOIN personzukat z1
    ON z1.zuperson=p.ident
  INNER JOIN kategorie k1
    ON k1.ident=z1.zukat
  LEFT JOIN personzukat z2
    ON z2.zuperson=p.ident
  LEFT JOIN kategorie k2
    ON (k2.ident=z2.zukat) AND (k2.kat LIKE '%zwei%')
  WHERE (
      (LOWER(k2.kat) LIKE '%eins%')
    AND ((k2.kat NOT LIKE '%zwei%') OR ISNULL(k2.ident))
    )

SELECT 
    *
FROM
    person p
JOIN
    personzukat z1
ON
    z1.zuperson = p.ident
JOIN
    kategorie k1
ON
    k1.ident = z1.zukat
JOIN
    personzukat z2
ON
    z2.zuperson = p.ident
LEFT JOIN
    kategorie k2
ON
    k2.ident=z2.zukat AND k2.kat LIKE '%zwei%'
WHERE
    LOWER(k2.kat) LIKE '%eins'
    AND
    k2.ident IS NULL

ON
    k2.ident=z2.zukat AND k2.kat LIKE '%drei%'

    
CREATE TABLE IF NOT EXISTS `kategorie` (
  `ident` bigint(20) unsigned NOT NULL auto_increment,
  `kat` varchar(25) collate utf8_bin NOT NULL default '',
  PRIMARY KEY  (`ident`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=6 ;

INSERT INTO `kategorie` (`ident`, `kat`) VALUES
(1, 'Frauenchor'),
(2, 'Kinderchor'),
(3, 'Jugendchor'),
(4, 'Jugendorchester'),
(5, 'Jugendblaskapelle');

CREATE TABLE IF NOT EXISTS `person` (
  `ident` bigint(20) unsigned NOT NULL auto_increment,
  `person` varchar(50) collate utf8_bin NOT NULL default '',
  PRIMARY KEY  (`ident`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=7 ;

INSERT INTO `person` (`ident`, `person`) VALUES
(1, 'Hans'),
(2, 'Franz'),
(3, 'Sepp'),
(4, 'Gerald'),
(5, 'Markus');

CREATE TABLE IF NOT EXISTS `personzukat` (
  `zuperson` bigint(20) unsigned NOT NULL default '0',
  `zukat` bigint(20) unsigned NOT NULL default '0',
  KEY `zuperson` (`zuperson`,`zukat`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

INSERT INTO `personzukat` (`zuperson`, `zukat`) VALUES
(1, 3),
(1, 4),
(2, 4),
(2, 5),
(3, 2),
(4, 3),
(4, 5),
(5, 2),
(5, 4),
(5, 5);

SELECT *
  FROM person p
  INNER JOIN personzukat z1
    ON z1.zuperson = p.ident
  INNER JOIN kategorie k1
    ON k1.ident = z1.zukat
  INNER JOIN personzukat z2
    ON z2.zuperson = p.ident
  LEFT JOIN kategorie k2
    ON k2.ident=z2.zukat AND k2.kat LIKE '%chor%'
  WHERE LOWER(k1.kat) LIKE '%jugend%'
    AND k2.ident IS NULL

ident | person | zuperson | zukat | ident | kat               | zuperson | zukat | ident | kat
1     | Hans   | 1        | 3     | 3     | Jugendchor        | 1        | 4     | NULL  | NULL
1     | Hans   | 1        | 4     | 4     | Jugendorchester   | 1        | 4     | NULL  | NULL
2     | Franz  | 2        | 4     | 4     | Jugendorchester   | 2        | 4     | NULL  | NULL
2     | Franz  | 2        | 4     | 4     | Jugendorchester   | 2        | 5     | NULL  | NULL
2     | Franz  | 2        | 5     | 5     | Jugendblaskapelle | 2        | 4     | NULL  | NULL
2     | Franz  | 2        | 5     | 5     | Jugendblaskapelle | 2        | 5     | NULL  | NULL
4     | Gerald | 4        | 3     | 3     | Jugendchor        | 4        | 5     | NULL  | NULL
4     | Gerald | 4        | 5     | 5     | Jugendblaskapelle | 4        | 5     | NULL  | NULL
5     | Markus | 5        | 4     | 4     | Jugendorchester   | 5        | 4     | NULL  | NULL
5     | Markus | 5        | 4     | 4     | Jugendorchester   | 5        | 5     | NULL  | NULL
5     | Markus | 5        | 5     | 5     | Jugendblaskapelle | 5        | 4     | NULL  | NULL
5     | Markus | 5        | 5     | 5     | Jugendblaskapelle | 5        | 5     | NULL  | NULL

SELECT *,
  (SELECT k2.kat FROM personzukat z2, kategorie k2
    WHERE ((z2.zukat=k2.ident) AND (z2.zuperson=p.ident))
      AND (k2.kat LIKE '%chor%') LIMIT 1
  ) AS isbad
  FROM person p
  INNER JOIN personzukat z
    ON z.zuperson = p.ident
  INNER JOIN kategorie k1
    ON k1.ident = z.zukat
  WHERE LOWER(k1.kat) LIKE '%jugend%'
  HAVING (ISNULL(isbad));