create temporary table gks
(
	id int(3),
	kurz char(2)
);


insert into gks
  select kg.kgid, kg.kurz from `llg-kursgrp` as kg
    where (left(kg.kurz,1) = "G") 
      and  (not (substring(kg.kurz, 2,1) = " ")) 
      and (not (substring(kg.kurz, 2,1) = "0")) ;

select * from gks;

create temporary table belegtekgs
(
	id int(3),
	kurz char(2)
);

insert into belegtekgs
  select gks.id, gks.kurz from gks
    inner join `llg-kursklasse` as kukla
      on (gks.id = kukla.grp_id) and (kukla.stufe = 11)
    inner join `llg-kurs` as kurs
      on (kurs.kukla_id = kukla.kkid)
    inner join `llg-fach`as fach
      on (fach.fid = kurs.fach_id) and (fach.kurz = "i1")
  group by gks.id;

select * from belegtekgs;

select * from `llg-kurs` as kursw
  where kursw.fach_id = 24;


create temporary table freiekgs
(
	id int(3),
	kurz char(2)
);

insert into freiekgs
  select gks.id, gks.kurz from gks
    left join belegtekgs
      on belegtekgs.id = gks.id
  where belegtekgs.id is null;

select * from freiekgs;

create temporary table biolehrer
(
	id int(3),
	kurz varchar(5)
);

insert into biolehrer
  select lehrer.lid, lehrer. kurz from `llg-lehrer`as lehrer
    inner join `llg-fakultas`as fakultas 
      on fakultas.lehrer_id = lehrer.lid
    inner join `llg-fach` as fach
      on fach.fid = fakultas.fach_id
  where fach.kurz = "I";


create temporary table freiekgstunden
(
	kgid int(3),
	tagstid varchar(5)
);



insert into freiekgstunden
  select kg.kgid, stupl.tagstd_id from `llg-stuplan`as stupl
    inner join `llg-kurs`as kurs 
      on kurs.kuid =  stupl.kurs_id
    inner join `llg-kursklasse` as kukla
      on kukla.kkid = kurs.kukla_id
    inner join `llg-kursgrp`as kg
      on kg.kgid = kukla.grp_id
    inner join freiekgs
      on freiekgs.id = kg.kgid
  where (kukla.stufe =  11) 
    and (not(stupl.tagstd_id = 56)) 
    and (not(stupl.tagstd_id = 20))
  group by kg.kgid, stupl.tagstd_id;


select * from freiekgstunden;


create temporary table kollisionen
(
	lid int(3),
	tagstid int(5),
	kgid int(3)
);

insert into kollisionen
  select lehrer.lid, stupl.tagstd_id, freiekgstunden.kgid from biolehrer
    inner join `llg-stuplan`as stupl
      on stupl.lehrer_id = biolehrer.id
    inner join `llg-kurs`as kurs 
      on kurs.kuid =  stupl.kurs_id
    inner join `llg-kursklasse` as kukla
      on kukla.kkid = kurs.kukla_id
    inner join `llg-kursgrp`as kg
      on kg.kgid = kukla.grp_id
    inner join freiekgstunden
      on freiekgstunden.tagstid = stupl.tagstd_id
    inner join `llg-lehrer`as lehrer
      on lehrer.lid = stupl.lehrer_id
  group by stupl.lehrer_id, freiekgstunden.kgid;


select * from kollisionen;

create temporary table alle
(
	lid int(3),
	lkurz varchar(5),
	kgid int(5),
	kgkurz varchar(5)
);

insert into alle
  select biolehrer.id ,biolehrer.kurz, freiekgs.id,freiekgs.kurz from biolehrer
    inner join freiekgs
      on 1=1;

select lkurz, kgkurz from alle
  left join kollisionen
    on (kollisionen.lid = alle.lid) and (kollisionen.kgid = alle.kgid) 
  where kollisionen.lid is null;