1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies. Weitere Informationen
  2. Unsere jährliche Weihnachts-Banner-Aktion hat begonnen! Wir freuen uns auf viele, viele kreative Vorschläge.
    Mehr dazu könnt Ihr hier nachlesen: Weihnachtsbanner 2016

    Information ausblenden

Komme bei einer SQL-Query nicht weiter :(

Dieses Thema im Forum "Web-Programmierung" wurde erstellt von davulb, 21.08.08.

  1. davulb

    davulb Boskoop

    Dabei seit:
    29.12.06
    Beiträge:
    39
    Hallo,

    ich möchte aus einer Tabelle die Datensätze auslesen und desweiteren Datensätze einer weiteren Abfrage anhängen. Ist sowas möglich?

    Die erste Abfrage lautet:
    Code:
    SELECT CONCAT(virtual_aliases.source, '@', virtual_domains.name) AS email,
        destination
    FROM virtual_aliases
    LEFT JOIN virtual_domains ON virtual_aliases.domain_id=virtual_domains.id;
    Nun sollen aus einer zweiten Abfrage Datensätze einer dritten Tabelle an der ersten Abfrage angehängt werden. Das Ausgabe-Scheme wäre das gleiche, also Spalte email und Spalte destination.

    Hat jemand einen Tipp?
     
  2. nevermind

    nevermind Bismarckapfel

    Dabei seit:
    19.12.07
    Beiträge:
    142
    Sorry, ich versteh's nicht ganz. Hast Du eine zweite virtual_aliases, die Du mit virtual_domains joinen willst, oder umgekehrt? Ich versteh' schon den left join an der Stelle nicht.
     
  3. Toddy

    Toddy Wohlschmecker aus Vierlanden

    Dabei seit:
    10.05.04
    Beiträge:
    240
    Etwas mehr Informationen wären ganz hilfreich ;)

    Wie sieht die Tabellenstruktur der drei Tabellen aus, und welche Felder möchtest du auslesen unter welchen Bedingungen?
     
  4. davulb

    davulb Boskoop

    Dabei seit:
    29.12.06
    Beiträge:
    39
    Meine Tabellen sehen wie folgt aus:

    Code:
    CREATE TABLE `virtual_domains` (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    active INT(1) NOT NULL DEFAULT '1',
    name VARCHAR(50) NOT NULL
    ) ENGINE = InnoDB;
    
    CREATE TABLE `virtual_users` (
    id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    domain_id INT(11) NOT NULL,
    user VARCHAR(40) NOT NULL,
    password VARCHAR(32) NOT NULL,
    CONSTRAINT UNIQUE_EMAIL UNIQUE (domain_id,user),
    FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
    ) ENGINE = InnoDB;
    
    CREATE TABLE `virtual_aliases` (
    id int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    domain_id INT(11) NOT NULL,
    source VARCHAR(40) NOT NULL,
    destination VARCHAR(80) NOT NULL,
    FOREIGN KEY (domain_id) REFERENCES virtual_domains(id) ON DELETE CASCADE
    ) ENGINE = InnoDB;
    
    CREATE VIEW view_users AS
    SELECT virtual_users.active, CONCAT(virtual_users.user, '@', virtual_domains.name) AS email,
        virtual_users.password
    FROM virtual_users
    	LEFT JOIN virtual_domains ON virtual_users.domain_id=virtual_domains.id 
    WHERE virtual_users.active=1;	
     
    CREATE VIEW view_aliases AS
    SELECT CONCAT(virtual_aliases.source, '@', virtual_domains.name) AS email,
        destination
    FROM virtual_aliases
        LEFT JOIN virtual_domains ON virtual_aliases.domain_id=virtual_domains.id;
    
    Das ganze dient für ein Mailserver-Setup bei dem der MTA die User, Aliase usw aus eine MySQL Datenbank ausliest. Hat zwar nicht direkt mit Webdesign o. ä. zu tun aber mir geht es in erster Linie um die SQL-Abfrage.

    Mit dem o. g. SQL Query
    Code:
    SELECT CONCAT(virtual_aliases.source, '@', virtual_domains.name) AS email,
        destination
    FROM virtual_aliases
    LEFT JOIN virtual_domains ON virtual_aliases.domain_id=virtual_domains.id;
    erhalte ich aus der Datenbank eine Tabelle mit allen möglichen Aliase (Email-Weiterleitungen) die der MTA dann weiterverarbeitet. Der beinhaltende Join-Befehl ruft die Domain aus einer anderen Tabelle ab.

    Ich werde jetzt die Tabelle virtual_users so abändern das es pro Benutzer eine Spalte mit Vor- und Nachname gibt damit man auch Trennungen wie . oder - einbauen kann. Und genau hier ist auch mein Problem. Damit Emails bei dem Benutzer jim.beam@example.com ankommen, der eigentliche Benutzername aber jimbeam@example.com lautet muss dafür ein seperater Datensatz in der virtual_aliases angelegt werden. Dies möchte ich automatisieren indem die SQL-Abfrage dies berücksichtigt und sozusagen automatisch dynamisch alle Benutzer aus virtual_users mit . oder - umwandelt. Wichtig ist das der gesamte Befehl in einer SQL-Abfrage verschachtelt ist.

    Hab heute schon ewig mit Joins, Subselects, IF-Clauses usw. herum probiert aber ich find einfach keinen Weg.

    Wär echt super wenn ihr eine Lösung findet. Seht es als kleine Herausforderung ;)
     
  5. Toddy

    Toddy Wohlschmecker aus Vierlanden

    Dabei seit:
    10.05.04
    Beiträge:
    240
    Ich muss gestehen, dass ich mir immer noch nicht sicher bin, dich richtig verstanden zu haben.

    Aber probiere doch mal folgendes, ob dir das weiterhilft:

    Code:
    SELECT CONCAT("vorname", ".", "nachname") AS alias UNION SELECT CONCAT("vorname", "-", "nachname") AS alias
     
  6. Bananenbieger

    Bananenbieger Golden Noble

    Dabei seit:
    14.08.05
    Beiträge:
    24.564
    Wäre es nicht einfacher, den Mailserver per passend zu konfigurieren?
     
  7. davulb

    davulb Boskoop

    Dabei seit:
    29.12.06
    Beiträge:
    39
    Werd das morgen mit dem Union Select mal probieren, hab nur grad die VM mit der Testkonfiguration nicht da.


    Was meinst Du damit? Händisch die Aliase anlegen? Das wäre zu aufwendig und ist auch nicht so schön zu administrieren. Die Idee mit der SQL Query wäre schon schöner


    Hier noch mal ein Versuch das ganze anhand von Testdaten zu erklären:

    Code:
    virtual_domains
    ----------------------------------------------------
     id  | name
    ----------------------------------------------------
     1   | example.com
    
    
    virtual_users
    ----------------------------------------------------
     id  | domain_id  | user    | user2    | password
    ----------------------------------------------------
     1   | 1          | jim     | beam     | md5(blabla)
    
    
    virtual_aliases
    ----------------------------------------------------
     id | domain_id   | source    | destination
    ----------------------------------------------------
     1  | 1           | jimbeam   | jimbeam@gmx.de
    So, und die Abfrage soll bewirken das ich folgende Ausgabe habe:
    Code:
    view_aliases
    ----------------------------------------------------
     email                  | destination
    ----------------------------------------------------
     jimbeam@example.com    | jimbeam@gmx.de
     jim.beam@example.com   | jimbeam@example.com
     jim-beam@example.com   | jimbeam@example.com
    Kann mir vorstellen das man dem Ganzen nicht so einfach folgen kann aber ich weiß nicht wie ich es sonst noch erklären kann :-[

    Hoffe nun ist es offensichtlich was mein Ziel ist.
     
  8. Toddy

    Toddy Wohlschmecker aus Vierlanden

    Dabei seit:
    10.05.04
    Beiträge:
    240
    Das solltest du mit einer Kombination aus CONCAT, wie du es ja bereits einsetzt, und UNION SELECT schaffen :)
     
  9. zeno

    zeno Lane's Prinz Albert

    Dabei seit:
    05.11.05
    Beiträge:
    4.898
    Wärs nicht einfacher die ganzen Sonderzeichen rauszuwerfen und zu schauen ob dann die Adresse existiert?
     
  10. davulb

    davulb Boskoop

    Dabei seit:
    29.12.06
    Beiträge:
    39
    Guten Morgen,

    so, vielen Dank an Toddy, der Tipp mit dem Union war Gold wert. Meine Abfrage liefert nun genau das was ich brauch. Danke, war echt schon am verzweifeln :)

    Die Abfrage sieht nun so aus:
    Code:
    SELECT
        CONCAT(virtual_aliases.source, '@', virtual_domains.name) AS email,
        destination
    FROM 
        virtual_aliases
    LEFT JOIN 
        virtual_domains ON virtual_aliases.domain_id=virtual_domains.id
    UNION
    SELECT 
        IF(virtual_users.user2<>"", CONCAT(virtual_users.user, ".", virtual_users.user2, '@', virtual_domains.name), CONCAT(virtual_users.user, virtual_users.user2, '@', virtual_domains.name)) AS email,
        CONCAT(virtual_users.user, virtual_users.user2, '@', virtual_domains.name) AS destination
    FROM 
        virtual_users
    LEFT JOIN 
        virtual_domains ON virtual_users.domain_id=virtual_domains.id
    WHERE 
        virtual_users.user2<>""
    UNION
    SELECT 
        IF(virtual_users.user2<>"", CONCAT(virtual_users.user, "-", virtual_users.user2, '@', virtual_domains.name), CONCAT(virtual_users.user, virtual_users.user2, '@', virtual_domains.name)) AS email,
        CONCAT(virtual_users.user, virtual_users.user2, '@', virtual_domains.name) AS destination
    FROM 
        virtual_users
    LEFT JOIN 
        virtual_domains ON virtual_users.domain_id=virtual_domains.id
    WHERE
        virtual_users.user2<>""
        
    ... und das Ergebnis:
    Code:
    view_aliases
    ----------------------------------------------------
     email                  | destination
    ----------------------------------------------------
     steve@example.com      | steve.miller@provider.com
     kerstin@foobar.org     | kerstin42@anotherprovider.com
     kerstin@foobar.org     | kerstin@mycompany.com
     jim.beam@example.com   | jimbeam@example.com
     jim-beam@example.com   | jimbeam@example.com
     

Diese Seite empfehlen