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;