Abfrage über zwei Tabellen

[micha_1984]

Hallo,
ich bin irgendwie langsam blind oder bl**.
Ich habe zwei Tabellen, eine Tabelle adressen und eine Tabelle telefon.
Nun muss ich eine Abfrage schreiben welche beide Kombiniert.
Die Tabelle Adresse hat folgende Reihen adr_id,adr_name, adr_zusatz, adr_strasse, adr_plz,adr_ort. adr_id ist hier der Index.
Die Tabelle telefon hat folgende Reihen te_id, te_bez_id, te_vorwahl, te_telefonr. te_id ist der hier der Index.

adr_id und te_bez_id sind die beiden Verknüpfenden Elemente. te_id sagt aus ob es eine Telefonnummer oder eine Faxnummer ist. 1 = Telefon, 2 = Fax.

Wenn ich die Möglichkeit hätte würde ich die Datenbankstruktur ändern nur leider kann ich das nicht, da es eine vorgegebene Struktur ist und ich nur eine Abfrage schreiben muss damit es genau eine Tabelle ausgibt die dann von einer anderen Anwendung ausgewertet werden kann.

Ich würde gerne die Telefonnummern jeweils passig zur Adresse ausgeben lassen, aber höchsten zwei mal.
Gibt es eine Möglichkeit das so zu realisieren? Bin ich da vielleicht einfach nur Blind um es zu sehen, nachdem ich fünf Stunden verschiedenste SQL Befehle getestet habe?

Es wäre super wenn mich irgendwer auf eine neue Idee bringe könnte, ich habe nämlich langsam keine Ideen mehr
Vielen dank.
Micha

 

[BerndB]

Hallo,

mal sehen ob ichs richtig verstanden habe.

select adr.*, tel.te_vorwahl as tel_vorwahl , tel.te_telefonr. te_id as tel_nr,
fax.te_vorwahl as fax_vorwahl , fax.te_telefonr. te_id as fax_nr
from Adresse adr
left join telefon tel on adr.adr_id = tel.te_bez_id and tel.te_id=1
left join telefon fax on adr.adr_id = tel.te_bez_id and tel.te_id=2;

Dies solle eine Zeile je Adresse anzeigen mit Telefon und Fax Nummer.

Gruss

Bernd

 

[BerndB]

UPS,

kleiner Fehler im 2ten JOIN

select adr.*, tel.te_vorwahl as tel_vorwahl , tel.te_telefonr. te_id as tel_nr,
fax.te_vorwahl as fax_vorwahl , fax.te_telefonr. te_id as fax_nr
from Adresse adr
left join telefon tel on adr.adr_id = tel.te_bez_id and tel.te_id=1
left join telefon fax on adr.adr_id = fax.te_bez_id and fax.te_id=2;


Gruss

Bernd

 

[ukulele]

Jetzt könnten natürlich zu einer Adresse noch mehrere Telefon und / oder Faxnummern in der Tabelle stehen dann würde die Adresse mehrfach ausgegeben werden. Oder gibt es da immer nur jeweils eine Telefon und eine Faxnummer?

 

[BerndB]

Stimmt,

wie sollen dann mehrfache tel / faxnummern angezeigt werden, oder sollen diese unterdrückt werden ?

Gruss

Bernd

 

[micha_1984]

Ich habe jetzt eine Abfrage mit Unterabfragen erstellt. Das funktioniert, solange ich die Auwahl auf ein Paar begrenze ...
Leider drückt das den armen Server bei Fast 6000 Einträgen in Tabelle T1 adressen fast in die Knie. Ich habe inzwischen auch herausgefunden das leider die Dokumentation nicht mehr richtig gepflegt wurde und dadurch noch ganz andere Einträge drin sind und ich langsam einfach nur noch Sauer über meinen Vorgänger bin.
Ich habe die gesamte Tabellen angesehe und keiner hat mehr als zwei Telefonnummern, einer Faxnummer, einer Mail Adresse pro Adresse und auch nur eine Webseite, usw.
Es ist leider kein sonderlich gutes Datenbankdesign für die neuen Anforderungen.

select T1.adr_id AS `EntryID`, T1.adr_id AS KDNR, T1.adr_name AS `Firma1`, T1.adr_zusatz AS `Firma2`,T1.adr_strasse AS `Strasse`,
T1.adr_plz AS `Plz`, T1.adr_ort AS `Ort`,
(select REPLACE(CONCAT(te_vorwahl,te_telefonr), ' ', '') from `telefon` where (T1.adr_id = telefon.te_bez_id) and tm_id=1 limit 1,1) AS `Rufnummer1`,
(select REPLACE(CONCAT(te_vorwahl,te_telefonr), ' ', '') from `telefon` where (T1.adr_id = telefon.te_bez_id) and tm_id=5 limit 1,1) AS `RufnummerFax`,
(select REPLACE(te_telefonr, ' ', '') from `telefon` where (T1.adr_id = telefon.te_bez_id) and tm_id=8 limit 1,1) AS `EmailAdresse`,
(select REPLACE(te_telefonr, ' ', '') from `telefon` where (T1.adr_id = telefon.te_bez_id) and tm_id=9 limit 1,1) AS `Webseite`
from `adressen` AS T1 WHERE `adr_archiv`='False'

Auf alle Fälle vielen Dank für die Antworten
Sollte irgendwer noch Ideen haben das zu beschleunigen immer her damit
Gruß
Micha

 

[BerndB]

Hallo Micha,

das sollte kein Problem zu sein die Abfrage zu tunen, wenn da nicht einige millionen Einträge je tabelle sind.

Poste mir doch bitte mal die Ausgabe der 3 Zeilen, dann schreib ich dir was du tun kannst.


SHOW CREATE TABLE adressen;

SHOW CREATE TABLE telefon;

EXPLAIN SELECT T1.adr_id AS `EntryID`, T1.adr_id AS KDNR, T1.adr_name AS `Firma1`, T1.adr_zusatz AS `Firma2`,T1.adr_strasse AS `Strasse`,
T1.adr_plz AS `Plz`, T1.adr_ort AS `Ort`,
(SELECT REPLACE(CONCAT(te_vorwahl,te_telefonr), ' ', '') FROM `telefon` WHERE (T1.adr_id = telefon.te_bez_id) AND tm_id=1 LIMIT 1,1) AS `Rufnummer1`,
(SELECT REPLACE(CONCAT(te_vorwahl,te_telefonr), ' ', '') FROM `telefon` WHERE (T1.adr_id = telefon.te_bez_id) AND tm_id=5 LIMIT 1,1) AS `RufnummerFax`,
(SELECT REPLACE(te_telefonr, ' ', '') FROM `telefon` WHERE (T1.adr_id = telefon.te_bez_id) AND tm_id=8 LIMIT 1,1) AS `EmailAdresse`,
(SELECT REPLACE(te_telefonr, ' ', '') FROM `telefon` WHERE (T1.adr_id = telefon.te_bez_id) AND tm_id=9 LIMIT 1,1) AS `Webseite`
FROM `adressen` AS T1 WHERE `adr_archiv`='False';

Gruss

Bernd

 

[micha_1984]

Hallo Bernd,
hier die Sachen:


adressenCREATE TABLE `adressen` (
`adr_id` int(11) NOT NULL AUTO_INCREMENT,
`adr_name` varchar(255) NOT NULL DEFAULT '',
`adr_zusatz` varchar(255) NOT NULL DEFAULT '',
`adr_strasse` varchar(255) NOT NULL DEFAULT '',
`adr_plz` varchar(10) NOT NULL DEFAULT '',
`adr_ort` varchar(255) NOT NULL DEFAULT '',
`adr_bemerk` text NOT NULL,
`adr_kunde` enum('True','False') NOT NULL DEFAULT 'False',
`adr_lieferant` enum('True','False') NOT NULL DEFAULT 'False',
`adr_personal` enum('True','False') NOT NULL DEFAULT 'False',
`adr_limit` varchar(255) NOT NULL DEFAULT '',
`adr_schwierig` enum('True','False') NOT NULL DEFAULT 'False',
`adr_archiv` enum('True','False') NOT NULL DEFAULT 'False',
`adr_angelegt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`adr_id`),
FULLTEXT KEY `adr_name` (`adr_name`)
) ENGINE=MyISAM AUTO_INCREMENT=8024 DEFAULT CHARSET=latin1

telefonCREATE TABLE `telefon` (
`te_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`te_bez` enum('adr','ansp') NOT NULL DEFAULT 'adr',
`te_bez_id` int(10) unsigned NOT NULL DEFAULT '0',
`te_vorwahl` varchar(255) NOT NULL DEFAULT '',
`te_telefonr` varchar(255) NOT NULL DEFAULT '',
`tm_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
`te_angelegt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`te_id`)
) ENGINE=MyISAM AUTO_INCREMENT=13626 DEFAULT CHARSET=latin1

idselect_typetabletypepossible_keyskeykey_lenrefrowsExtra
1PRIMARYT1ALLNULLNULLNULLNULL5224Using where
5DEPENDENT SUBQUERYtelefonALLNULLNULLNULLNULL13260Using where
4DEPENDENT SUBQUERYtelefonALLNULLNULLNULLNULL13260Using where
3DEPENDENT SUBQUERYtelefonALLNULLNULLNULLNULL13260Using where
2DEPENDENT SUBQUERY

Hier einmal alles was du angefragt hast.
Danke fürs Helfen
Gruß
Micha

 

[BerndB]

Hallo Micha,

da liegt so einiges im argen. als erstes solltest du einen Index setzten. Damit wird
das ganze schon mal um faktoren schneller

ALTER TABLE telefon ADD INDEX idx_te_bez_id (te_bez_id);

Die Ausgabe von

SELECT version();

kannst du mir bitte auch noch schicken.

Weiterhin sollte man mal schauen ob man den Tabellentyp nicht auf InnoDB umstellt. Dadurch bist du besser gegen Datenverlust geschützt. Und halt noch das Query optimieren.

Aber Schritt für Schritt

Gruss

Bernd

 

[micha_1984]

Hallo Bernd,
der Index ist erzeugt.
Die Version ist 5.1.73-1
Es ist leider nicht ganz einfach ein Update von dem Debian zu machen, da die darauf laufende Anwendung Register_Globals benötig. Sie wird zwar in ein oder zwei jahren ersetzt, aber bis dahin muss ich leider damit noch arbeiten.
Ich könnte den Tabellentyp durchaus zu InnoDB machen, das ist alles nur mit der Zeit immer so gewachsen.
Gruß
Micha

 

[BerndB]

Hi Micha,

ist das Query schneller geworden ?

Schick mir bitte noch mal das EXPLAIN.

Die Umstellung auf InnoDB ist nicht so einfach, da muss man halt noch einige Sachen
beächten. Ein MySQL update der Version 5.1 auf eine aktuellere ist aber angesagt oder eventuell ein Umstiegt auf MariaDB. Die voll kompatiebel, jedoch schneller.

Ist das Ergebnis deines Querys denn OK?, dann stell ich das noch kurz um, oder sollte das neue mehr können.

Können ja ggf. auch mal kurz telefonieren. Geht bestimmt schneller.

Gruss

Bernd

 

[micha_1984]

Hi Bernd,
das war Krass. Die Abfrage war jetzt weit unter 1 Sekunde. Vorher waren die Abfragen für 30 schon bei fast 3 Sekunden.
1PRIMARYT1ALLNULLNULLNULLNULL5224Using where
5DEPENDENT SUBQUERYtelefonrefidx_te_bez_ididx_te_bez_id4datenbank1.T1.adr_id3Using where
4DEPENDENT SUBQUERYtelefonrefidx_te_bez_ididx_te_bez_id4datenbank1.T1.adr_id3Using where
3DEPENDENT SUBQUERYtelefonrefidx_te_bez_ididx_te_bez_id4datenbank1.T1.adr_id3Using where
2DEPENDENT SUBQUERYtelefonrefidx_te_bez_ididx_te_bez_id4datenbank1.T1.adr_id3Using where
Ich habe die Abfrage jetzt nochmal an die Vorgaben angepasst, dann sieht sie im moment so aus und liefert genau das was sie soll

select T1.adr_id AS `EntryID`, T1.adr_id AS KDNR, T1.adr_name AS `Firma1`, T1.adr_zusatz AS `Firma2`,T1.adr_strasse AS `Strasse`,
T1.adr_plz AS `Plz`, T1.adr_ort AS `Ort`,
(select REPLACE(CONCAT(te_vorwahl,te_telefonr), ' ', '') from `telefon` where (T1.adr_id = telefon.te_bez_id) and tm_id=1 limit 1,1) AS `Rufnummer1`,
(select REPLACE(CONCAT(te_vorwahl,te_telefonr), ' ', '') from `telefon` where (T1.adr_id = telefon.te_bez_id) and tm_id=1 limit 2,1) AS `Rufnummer2`,
(select REPLACE(CONCAT(te_vorwahl,te_telefonr), ' ', '') from `telefon` where (T1.adr_id = telefon.te_bez_id) and tm_id=1 limit 2,1) AS `Rufnummer3`,
(select REPLACE(CONCAT(te_vorwahl,te_telefonr), ' ', '') from `telefon` where (T1.adr_id = telefon.te_bez_id) and tm_id=5 limit 1,1) AS `RufnummerFax1`,
(select REPLACE(CONCAT(te_vorwahl,te_telefonr), ' ', '') from `telefon` where (T1.adr_id = telefon.te_bez_id) and tm_id=5 limit 2,1) AS `RufnummerFax2`,
(select REPLACE(te_telefonr, ' ', '') from `telefon` where (T1.adr_id = telefon.te_bez_id) and tm_id=8 limit 1,1) AS `EmailAdresse1`,
(select REPLACE(te_telefonr, ' ', '') from `telefon` where (T1.adr_id = telefon.te_bez_id) and tm_id=8 limit 2,1) AS `EmailAdresse2`,
(select REPLACE(te_telefonr, ' ', '') from `telefon` where (T1.adr_id = telefon.te_bez_id) and tm_id=8 limit 3,1) AS `EmailAdresse3`,
(select REPLACE(te_telefonr, ' ', '') from `telefon` where (T1.adr_id = telefon.te_bez_id) and tm_id=9 limit 1,1) AS `Webseite`
from `adressen` AS T1 WHERE `adr_archiv`='False'

Ich werde mal sehen ob ich ein Backport von dem MySQL bauen und einspielen kann.
Gruß
Micha

 

[micha_1984]

Ich musste noch eine kleine Änderung in der Abfrage vornehmen, aber seitdem läuft es jetzt richtig
select T1.adr_id AS `EntryID`, T1.adr_id AS KDNR, T1.adr_name AS `Firma1`, T1.adr_zusatz AS `Firma2`,T1.adr_strasse AS `Strasse`,
T1.adr_plz AS `Plz`, T1.adr_ort AS `Ort`,
(select REPLACE(CONCAT(te_vorwahl,te_telefonr), ' ', '') from `telefon` where (T1.adr_id = telefon.te_bez_id) and tm_id=1 limit 0,1) AS `Rufnummer1`,
(select REPLACE(CONCAT(te_vorwahl,te_telefonr), ' ', '') from `telefon` where (T1.adr_id = telefon.te_bez_id) and tm_id=1 limit 1,1) AS `Rufnummer2`,
(select REPLACE(CONCAT(te_vorwahl,te_telefonr), ' ', '') from `telefon` where (T1.adr_id = telefon.te_bez_id) and tm_id=1 limit 2,1) AS `Rufnummer3`,
(select REPLACE(CONCAT(te_vorwahl,te_telefonr), ' ', '') from `telefon` where (T1.adr_id = telefon.te_bez_id) and tm_id=5 limit 0,1) AS `RufnummerFax1`,
(select REPLACE(CONCAT(te_vorwahl,te_telefonr), ' ', '') from `telefon` where (T1.adr_id = telefon.te_bez_id) and tm_id=5 limit 1,1) AS `RufnummerFax2`,
(select REPLACE(CONCAT(te_vorwahl,te_telefonr), ' ', '') from `telefon` where (T1.adr_id = telefon.te_bez_id) and tm_id=2 limit 0,1) AS `RufnummerHandy`,
(select REPLACE(te_telefonr, ' ', '') from `telefon` where (T1.adr_id = telefon.te_bez_id) and tm_id=8 limit 0,1) AS `EmailAdresse1`,
(select REPLACE(te_telefonr, ' ', '') from `telefon` where (T1.adr_id = telefon.te_bez_id) and tm_id=8 limit 1,1) AS `EmailAdresse2`,
(select REPLACE(te_telefonr, ' ', '') from `telefon` where (T1.adr_id = telefon.te_bez_id) and tm_id=8 limit 2,1) AS `EmailAdresse3`,
(select REPLACE(te_telefonr, ' ', '') from `telefon` where (T1.adr_id = telefon.te_bez_id) and tm_id=9 limit 0,1) AS `Webseite`
from `adressen` AS T1 WHERE `adr_archiv`='False' ORDER BY `EntryID`