Ansicht GroupMembers

Abfrage

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