Abfrage über zwei Tabellen

[Jan_ed26d8]

Hallo Experten,
ich bastele gerade mit MySQL eine Schriftsteller-DB zusammen. Dabei habe ich die zwei Probleme, dass es zum einen verschiedene Schreibweisen geben kann (Tschechow = Cechov) und zum anderen benutzen Schriftsteller auch Pseudonyme (Peter Panter = Tucholsky).
Als erste Lösung wollte ich zwei Tabellen nehmen, eine Tab. „bio“ mit den biographischen Daten und eine zweite „varianten“, mit den verschiedenen Namensformen:

DROP TABLE IF EXISTS bio;
CREATE TABLE IF NOT EXISTS bio (
 id int(10) unsigned NOT NULL auto\_increment,
 name varchar(255) collate utf8\_unicode\_ci NOT NULL,
 vorname varchar(255) collate utf8\_unicode\_ci default NULL, 
 geb date NOT NULL,
 gest date,
 PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8\_unicode\_ci;

DROP TABLE IF EXISTS varianten;
CREATE TABLE IF NOT EXISTS varianten (
 id int(10) unsigned NOT NULL auto\_increment,
 bio\_id int(10) unsigned NOT NULL,
 name varchar(255) collate utf8\_unicode\_ci NOT NULL,
 vorname varchar(255) collate utf8\_unicode\_ci default NULL,
 PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8\_unicode\_ci;

wobei bio_id = bio.id sein soll.

Noch ein paar Testdaten zum Befüllen:

INSERT INTO bio (name, vorname, geb, gest) VALUES
 ('Tucholsky', 'Kurt', '1890-01-09', '1935-12-21'),
 ('Tschechow', 'Anton Pawlowitsch', '1860-01-17', '1904-07-15'),
 ('Schiller', 'Friedrich', '1759-11-10', '1805-05-09');

INSERT INTO varianten (bio\_id, name, vorname) VALUES
 (1, 'Panter', 'Peter'),
 (1, 'Tiger', 'Theobald'),
 (1, 'Wrobel', 'Ignaz'),
 (2, 'Cechov', 'Anton Pavlovic');

Gefordert ist nun, dass sich der Abfragende nicht darum kümmern braucht, ob es sich um ein Pseudonym oder eine Namensvariante handelt. Ob er nun Cechov oder Tschechow eingibt, er soll er die richtigen Lebensdaten erhalten. Ebenso bei der Eingabe von Peter Panter die Daten von Tucholsky, im Idealfall natürlich um die Pseudonymenliste ergänzt (aber das wäre dann schon der zweite Schritt).
Folgende, etwas vereinfachte Abfrage habe ich schon:

SELECT bio.\*, varianten.name FROM bio, varianten
WHERE bio\_id = bio.id AND (varianten.name = 'Tucholsky' OR bio.name = 'Tucholsky');

Das funktioniert für alle Namen, die „Varianten“ besitzen, ganz gut, gibt aber wegen „bio_id = bio.id“ z.B. bei Schiller ein leeres Ergebnis zurück.
Ist mein Tabellen-Modell falsch, sollte meine Abfrage anders sein oder lässt sich das mit MySQL-Hausmitteln gar nicht lösen?
Ich komme einfach nicht weiter und wäre dankbar für jede Hilfe.

Vielen Dank schon mal
Jan

[RakonDark_2d341e]

INSERT INTO bio (name, vorname, geb, gest) VALUES
(‚Tucholsky‘, ‚Kurt‘, ‚1890-01-09‘, ‚1935-12-21‘),
(‚Tschechow‘, ‚Anton Pawlowitsch‘, ‚1860-01-17‘,
‚1904-07-15‘),
(‚Schiller‘, ‚Friedrich‘, ‚1759-11-10‘, ‚1805-05-09‘);

INSERT INTO varianten (bio_id, name, vorname) VALUES
(1, ‚Panter‘, ‚Peter‘),
(1, ‚Tiger‘, ‚Theobald‘),
(1, ‚Wrobel‘, ‚Ignaz‘),
(2, ‚Cechov‘, ‚Anton Pavlovic‘);

Gefordert ist nun, dass sich der Abfragende nicht darum
kümmern braucht, ob es sich um ein Pseudonym oder eine
Namensvariante handelt.

SELECT * FROM bio left outer join varianten on bio.id = varianten.bio_id where bio.name=’’ or varianten.name=’’ GROUP by bio.id

sollte für alle varianten oder bio immer einen datensatz liefrn.

logik

wir haben auf jedenfall einen bio den wir suchen

bio left outer join varianten on bio.id = varianten.bio_id

heist such alle aus bio und ergänze sie wenn vorhanden mit daten aus varianten

nun haben wir alle bios mit jeweils auch ihren varianten

also

id name vorname geb gest id bio\_id name vorname
1 Tucholsky Kurt 1890-01-09 1935-12-21 1 1 Panter Peter
1 Tucholsky Kurt 1890-01-09 1935-12-21 2 1 Tiger Theobald
1 Tucholsky Kurt 1890-01-09 1935-12-21 3 1 Wrobel Ignaz
2 Tschechow Anton Pawlowitsch 1860-01-17 1904-07-15 4 2 Cechov Anton Pavlovic
3 Schiller Friedrich 1759-11-10 1805-05-09 NULL NULL NULL NULL

so nun filtern

WHERE bio.name=‚Tucholsky‘ or varianten.name=’

das ergibt

id name vorname geb gest id bio\_id name vorname
1 Tucholsky Kurt 1890-01-09 1935-12-21 1 1 Panter Peter
1 Tucholsky Kurt 1890-01-09 1935-12-21 2 1 Tiger Theobald
1 Tucholsky Kurt 1890-01-09 1935-12-21 3 1 Wrobel Ignaz

nun brauchen wir aber nur einen datensatz und eigentlich brauchen wir auch nur die bio.id , weil nach der ist ja eigentlich gesucht.

GROUP by bio.id

thats it

wenn du nur eine variante gibt es ja nur einen datensatz

[Jan_ed26d8]

Hallo RakonDark,
vielen Dank für die kompentente und ausführliche Antwort. Bei left join verknoten sich immer meine linke und rechte Gehirnhälfte Aber ich denke, jetzt habe ich das endlich mal verstanden.

Viele Grüsse
Jan