/* Alle GK-Rastergruppen der Stufe 11 */ 
/* (denkbar ist auch, hier bereits die tagstd_id zu holen) */ 
-- 
CREATE TEMPORARY TABLE `tmp_alle` (
grp_id INT( 2 ) NOT NULL
);
INSERT INTO `tmp_alle`
  SELECT kukla.grp_id
    FROM `llg-stuplan` AS sp
    INNER JOIN `llg-kurs` AS ku ON ku.kuid = sp.kurs_id
    INNER JOIN `llg-kursklasse` AS kukla ON kukla.kkid = ku.kukla_id
    INNER JOIN `llg-kursgrp` AS kg ON kg.kgid = kukla.grp_id
  WHERE (kukla.stufe =11)
    AND (LEFT( kg.kurz, 1 ) = 'G')
    AND NOT (LEFT( kg.kurz, 2 ) = 'g ')
    AND NOT (LEFT( kg.kurz, 2 ) = 'G0')
    AND NOT (SUBSTRING( ku.lang, 4, 1 ) = 'Y')
  GROUP BY kukla.grp_id;
  
/* Alle GK-Rastergruppen der Stufe 11 mit Italienischkursen */ 
/* (denkbar ist auch, hier bereits die tagstd_id zu holen) */ 
-- 
CREATE TEMPORARY TABLE `tmp_ital`(
  grp_id INT( 2 ) NOT NULL
  );

INSERT INTO `tmp_ital`
  SELECT kukla.grp_id
    FROM `llg-stuplan` AS sp
      INNER JOIN `llg-kurs` AS ku ON ku.kuid = sp.kurs_id
      INNER JOIN `llg-kursklasse` AS kukla ON kukla.kkid = ku.kukla_id
      INNER JOIN `llg-kursgrp` AS kg ON kg.kgid = kukla.grp_id
      INNER JOIN `llg-fach` AS fa ON fa.fid = ku.fach_id
  WHERE (LEFT( fa.kurz, 2 ) = 'I1')
    AND (kukla.stufe = '11')
  /*AND NOT (SUBSTRING( ku.lang, 4, 1 ) = 'Y')*/
  GROUP BY kukla.grp_id
  ;

/* Komplementärtabelle zu (2): GK-Rastergruppen der Stufe 11 ohne I1-Kurse*/ 
SELECT * FROM tmp_alle AS alle
  LEFT JOIN tmp_ital AS ital ON alle.grp_id = ital.grp_id
  WHERE ital.grp_id IS NULL
  ;