Komme bei einer SQL-Query nicht weiter :(

davulb

Boskoop
Registriert
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?
 

nevermind

Bismarckapfel
Registriert
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.
 

Toddy

Wohlschmecker aus Vierlanden
Registriert
10.05.04
Beiträge
242
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?
 

davulb

Boskoop
Registriert
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 [email protected] ankommen, der eigentliche Benutzername aber [email protected] 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 ;)
 

Toddy

Wohlschmecker aus Vierlanden
Registriert
10.05.04
Beiträge
242
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
 

Bananenbieger

Golden Noble
Registriert
14.08.05
Beiträge
25.515
Wäre es nicht einfacher, den Mailserver per passend zu konfigurieren?
 

davulb

Boskoop
Registriert
29.12.06
Beiträge
39
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

Werd das morgen mit dem Union Select mal probieren, hab nur grad die VM mit der Testkonfiguration nicht da.


Wäre es nicht einfacher, den Mailserver per passend zu konfigurieren?

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   | [email protected]

So, und die Abfrage soll bewirken das ich folgende Ausgabe habe:
Code:
view_aliases
----------------------------------------------------
 email                  | destination
----------------------------------------------------
 [email protected]    | [email protected]
 [email protected]   | [email protected]
 [email protected]   | [email protected]

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.
 

Toddy

Wohlschmecker aus Vierlanden
Registriert
10.05.04
Beiträge
242
[...]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.

Das solltest du mit einer Kombination aus CONCAT, wie du es ja bereits einsetzt, und UNION SELECT schaffen :)
 

zeno

Lane's Prinz Albert
Registriert
05.11.05
Beiträge
4.894
Wärs nicht einfacher die ganzen Sonderzeichen rauszuwerfen und zu schauen ob dann die Adresse existiert?
 

davulb

Boskoop
Registriert
29.12.06
Beiträge
39
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

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
----------------------------------------------------
 [email protected]      | [email protected]
 [email protected]     | [email protected]
 [email protected]     | [email protected]
 [email protected]   | [email protected]
 [email protected]   | [email protected]