CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `proacc_promoter` AS
select
    `pj`.`jobnummer` AS `JobNr`,
    `p`.`id` AS `PromoterID`,
    1 AS `AgenturId`,
    `p`.`kreditorennummer` AS `kreditorennummer`,
    (case
        `p`.`geschlecht`
        when _latin1'1' then _latin1'1'
        when _latin1'0' then _latin1'2' end) AS `Anrede`,
    `p`.`nachname` AS `nachname`,
    `p`.`vorname` AS `vorname`,
    date_format(`p`.`geburtstag`, _latin1'%d.%m.%Y') AS `geburtstag`,
    `p`.`strasse` AS `strasse`,
    `p`.`plz` AS `plz`,
    `p`.`ort` AS `ort`,
    _latin1'' AS `firma`,
    `p`.`land` AS `tel`,
    `p`.`fax` AS `fax`,
    `p`.`mobil` AS `mobil`,
    `p`.`email` AS `email`,
    `p`.`bemerkung` AS `bemerkung`
from
    (((`promotionpromoter` `pp`
join `promoter` `p` on
    ((`p`.`id` = `pp`.`id_promoter`)))
join `promotion_jobnummer` `pj` on
    ((`pp`.`id_promotion` = `pj`.`id_promotion`)))
join `promotion` `pr` on
    ((`pr`.`id` = `pp`.`id_promotion`)))
where
    (`pj`.`archiv` <> 1)