66 lines
1.6 KiB
SQL
Executable File
66 lines
1.6 KiB
SQL
Executable File
-- An welchen Dienststellen waren die Studenten im Jahrgang 08
|
|
|
|
SELECT t1.saaid
|
|
, w.bezeichnung
|
|
, t1.uid
|
|
, x.vorname
|
|
, x.nachname
|
|
, t1.beginn
|
|
, t1.ende
|
|
, t1.abteilung
|
|
, x.hs
|
|
, y.bez
|
|
, y.str
|
|
, y.plz
|
|
, y.postfach
|
|
, y.plz_postfach
|
|
, y.ort
|
|
, y.mail
|
|
FROM stan_antrag t1, stan_vertiefungsbereich w, stud x, stan_dienststellen y, stan_stellenart z
|
|
WHERE t1.uid=x.uid
|
|
AND t1.dst_id=y.dst_id
|
|
AND t1.vert_bereich=w.vbid
|
|
AND z.artid=t1.art
|
|
AND z.artid=6
|
|
AND x.Jahrgang='$jahrgang'
|
|
AND t1.status = (
|
|
SELECT MAX(t2.status)
|
|
FROM stan_antrag t2
|
|
WHERE t1.aendid = t2.aendid
|
|
AND t2.status !='2'
|
|
)
|
|
AND (t1.zuweisung is NULL OR t1.zuweisung='Z')
|
|
union
|
|
SELECT t1.saaid
|
|
, w.bezeichnung
|
|
, t1.uid
|
|
, x.vorname
|
|
, x.nachname
|
|
, t1.beginn
|
|
, t1.ende
|
|
, t1.abteilung
|
|
, x.hs
|
|
, y.bez
|
|
, y.str
|
|
, y.plz
|
|
, y.postfach
|
|
, y.plz_postfach
|
|
, y.ort
|
|
, NULL mail
|
|
FROM stan_antrag t1, stud x, stan_vertiefungsbereich w, stan_dienststellen_tmp y, stan_stellenart z
|
|
WHERE t1.uid=x.uid
|
|
AND t1.dst_id=y.dst_id
|
|
AND t1.vert_bereich=w.vbid
|
|
AND z.artid=t1.art
|
|
AND z.artid=6
|
|
AND x.Jahrgang='$jahrgang'
|
|
AND t1.status = (
|
|
SELECT MAX(t2.status)
|
|
FROM stan_antrag t2
|
|
WHERE t1.aendid = t2.aendid
|
|
AND t2.status !='2'
|
|
)
|
|
AND (t1.zuweisung is NULL OR t1.zuweisung='Z')
|
|
ORDER BY hs, uid, beginn
|
|
|
|
|