Liste aller Abos (Depot-, Post und Heimlieferungen) wie Ansicht Kunde/Abos jedoch mit allen Ansprechpersonen und deren Email-Adresse.
SELECT
k.id AS KNr,
k.bezeichnung AS Name,
CONCAT_WS(
', ',
CONCAT_WS(' ', Person.vorname, Person.name),
Person.email
) AS Ansprechperson,
CONCAT('DEPOT') AS Lieferart,
depAbo.abotyp_name AS Abotyp,
depAbo.zusatz_abotyp_names AS Zusatzabos,
depAbo.id AS AboNr,
depAbo.start AS Start,
depAbo.ende AS Ende,
depAbo.aktiv AS Aktiv,
depAbo.vertrieb_beschrieb AS Vertrieb,
depAbo.depot_name AS AboName
FROM
Kunde AS k
RIGHT JOIN DepotlieferungAbo AS depAbo ON k.id = depAbo.kunde_id
LEFT JOIN Person ON k.id = Person.kunde_id
UNION
SELECT
k.id,
k.bezeichnung,
CONCAT_WS(
', ',
CONCAT_WS(' ', Person.vorname, Person.name),
Person.email
) AS ansprechperson,
concat('HEIM'),
heiAbo.abotyp_name AS HeiTyp,
heiAbo.zusatz_abotyp_names,
heiAbo.id,
heiAbo.start,
heiAbo.ende,
heiAbo.aktiv,
heiAbo.vertrieb_beschrieb,
heiAbo.tour_name
FROM
Kunde as k
RIGHT JOIN HeimlieferungAbo AS heiAbo on k.id = heiAbo.kunde_id
LEFT JOIN Person ON k.id = Person.kunde_id
UNION
SELECT
k.id,
k.bezeichnung,
CONCAT_WS(
', ',
CONCAT_WS(' ', Person.vorname, Person.name),
Person.email
),
CONCAT('POST'),
posAbo.abotyp_name,
posAbo.zusatz_abotyp_names,
posAbo.id,
posAbo.start,
posAbo.ende,
posAbo.aktiv,
posAbo.vertrieb_beschrieb,
CONCAT(' ')
FROM
Kunde AS k
RIGHT JOIN PostlieferungAbo AS posAbo ON k.id = posAbo.kunde_id
LEFT JOIN Person ON k.id = Person.kunde_id
ORDER BY
KNr;