Ansicht GroupMembers

Abfrage

??? info "SQL"

``` sql

  SELECT 
    GM."Mandant", GM."ID", GM."EnbreaID", 
    GM."Group", G."Code", G."Name",
    GM."MemberID", GM."MemberType", M."Vorname", M."Nachname", M."Strasse",
    M."Land", M."PLZ", M."Ort", M."Telefon", M."Email",
    GM."StartedAt", GM."FinishedAt", GM."Contribution", GM."ContributionPaidOn",
    GM."Function1", GM."Function2"
  FROM "tblGroupMembers" GM
  LEFT JOIN "Groups" G
  ON  G."Mandant" = GM."Mandant"
  AND G."ID" = GM."Group"
  LEFT JOIN "Personen" M
  ON  M."Mandant" = GM."Mandant"
  AND M."ID" = GM."MemberID"
  WHERE
    GM."MemberType" = 1
  UNION
  SELECT 
    GM."Mandant", GM."ID", GM."EnbreaID", 
    GM."Group", G."Code", G."Name",
    GM."MemberID", GM."MemberType", M."Vorname", M."Nachname", M."Strasse",
    M."Land", M."PLZ", M."Ort", M."Telefon", M."Email",
    GM."StartedAt", GM."FinishedAt", GM."Contribution", GM."ContributionPaidOn",
    GM."Function1", GM."Function2"
  FROM "tblGroupMembers" GM
  LEFT JOIN "Groups" G
  ON  G."Mandant" = GM."Mandant"
  AND G."ID" = GM."Group"
  LEFT JOIN "Lehrer" M
  ON  M."Mandant" = GM."Mandant"
  AND M."ID" = GM."MemberID"
  WHERE
    GM."MemberType" = 2  
  UNION
  SELECT 
    GM."Mandant", GM."ID", GM."EnbreaID", 
    GM."Group", G."Code", G."Name",
    GM."MemberID", GM."MemberType", M."Vorname", M."Nachname", M."Strasse",
    M."Land", M."PLZ", M."Ort", M."Telefon", M."EMail",
    GM."StartedAt", GM."FinishedAt", GM."Contribution", GM."ContributionPaidOn",
    GM."Function1", GM."Function2"
  FROM "tblGroupMembers" GM
  LEFT JOIN "Groups" G
  ON  G."Mandant" = GM."Mandant"
  AND G."ID" = GM."Group"
  LEFT JOIN "Schueler" M
  ON  M."Mandant" = GM."Mandant"
  AND M."ID" = GM."MemberID"
  WHERE
    GM."MemberType" = 3      
  UNION
  SELECT 
    GM."Mandant", GM."ID", GM."EnbreaID", 
    GM."Group", G."Code", G."Name",
    GM."MemberID", GM."MemberType", M."Vorname", M."Nachname", M."Strasse",
    M."Land", M."PLZ", M."Ort", M."TelefonPrivat", M."Email",
    GM."StartedAt", GM."FinishedAt", GM."Contribution", GM."ContributionPaidOn",
    GM."Function1", GM."Function2"
  FROM "tblGroupMembers" GM
  LEFT JOIN "Groups" G
  ON  G."Mandant" = GM."Mandant"
  AND G."ID" = GM."Group"
  LEFT JOIN "Sorgeberechtigte" M
  ON  M."Mandant" = GM."Mandant"
  AND M."ID" = GM."MemberID"
  WHERE
    GM."MemberType" = 4
```