✔ MySQL Suche über drei Tabellen

[Sprint]

Hallo zusammen,

der Titel hört sich eigentlich banal an und ist es vielleicht auch. Ich komme aber nicht auf die Lösung.

Folgendes Problem: Gegeben ist eine Kundentabelle, in der bisher alle auftretenden Daten zusammengefasst waren, also Name, Adresse, Telefon, Fax, Email usw. Die Kontaktdaten waren über die Zeit auf jeweils 5 Einträge erweitert words, was zum Schluß aber nicht mehr praktikabel war. Zumal immer mehr dazukommen, die 6 und mehr Telefonnummern haben.

Nun sind Telefon, Fax, Mobilfunk in eine Tabelle (gekennzeichnet mit T, F, M) und die Emails in eine separate Tabelle ausgelagert worden. Verknüpft jeweils über die Kundennummer. Soweit funktioniert das auch alles.

Jetzt existiert aber eine Funktion, mit der in den Datensätzen suchen kann. War bisher kein Problem, da ja alle durchsuchten Werte in einer Tabelle standen. Nun aber läuft die Suche über drei Tabellen, (kunde, email und telekom), bei der unterschiedlich viele Ergebnisse auftauchen können (Wenn z.B. eine Nummer für Telefon und Fax eingetragen ist). Und es kann sein, daß eine Telefonnummer oder Email für mehrere Kunden eingetragen ist.

Ich bringe es nicht zusammen, daß in einem Durchgang alle drei Tabellen durchsucht werden und alle anzuzeigenden Daten auf ein Mal ausgelesen werden (das sind so die üblichen Dinge wie Kundennummer, Name, Adresse, erste Emailadresse und erste Telefonnummer). Entweder beschwert er sich, daß in einem eingebetteten Select mehrere Ergebnisse auftreten oder er findet einfach nichts.

Natürlich könnte man alle drei Teile separat durchsuchen und die Ergebnisse hinterher zusammenführen, doch gibt das für mich ein viel zu umständliches Konstrukt.

Hat jemand eine Idee, wie das zusammengefasst werden kann?

Danke schon mal im Voraus,
Sprint

 

[BaseBallBatBoy]

Poste mal dein SQL welches du momentan verwendest. Dann kann man sich das auch besser vorstellen.
Was suchst du denn genau? IDs? Telefonnummern kannst du ja nicht bei den Emails suchen. Oder hast du mehrere Werte die du gleichzeitig suchen willst?

 

[Sprint]

Das hier ist das SQL, das im Moment noch auf die alte Konfiguration läuft.

select kunden.kdnr, risiko, kunden.vorname, kunden.nachname, firmenname, strasse, hausnr , plz, ort, gesperrt, uid, vortel1, tel1, login from kunden, gfs where 
$uidnummer (
kunden.kdnr like '$nummer' and 
kunden.vorname like '$vorname' and 
((kunden.nachname like '$nachname' or firmenname like '$nachname' or alteabholer like '$nachname') or
(gfs.nachname like '$nachname' and gfs.vorname like '$vorname' and kunden.kdnr = gfs.kdnr)) and 
(vortel1 like '$telefon' or tel1 like '$telefon' or 
vortel2 like '$telefon' or tel2 like '$telefon' or 
vortel3 like '$telefon' or tel3 like '$telefon' or 
vortel4 like '$telefon' or tel4 like '$telefon' or 
vortel5 like '$telefon' or tel5 like '$telefon' or 
vorfax1 like '$telefon' or fax1 like '$telefon' or 
vorfax2 like '$telefon' or fax2 like '$telefon' or 
vorfax3 like '$telefon' or fax3 like '$telefon' or 
vorfax4 like '$telefon' or fax4 like '$telefon' or 
vorfax5 like '$telefon' or fax5 like '$telefon' or 
vorhandy1 like '$telefon' or handy1 like '$telefon' or 
vorhandy2 like '$telefon' or handy2 like '$telefon' or 
vorhandy3 like '$telefon' or handy3 like '$telefon' or 
vorhandy4 like '$telefon' or handy4 like '$telefon' or 
vorhandy5 like '$telefon' or handy5 like '$telefon' or 
email1 like '$telefon' or email2 like '$telefon' or email3 like '$telefon' or email4 like '$telefon' or email5 like '$telefon') and 
(ort like '$ort' or plz like '$ort' or strasse like '$ort')) and geloescht = '0' group by kunden.kdnr;";

Telefon, Fax, Mobil bzw. Email sind halt jetzt in eigene Tabellen ausgelagert.

Das hier habe ich zuletzt probiert und habe damit regelmäßig den DB Server abgeschossen.

select kunden.kdnr, risiko, kunden.vorname, kunden.nachname, firmenname, strasse, hausnr, plz, ort, gesperrt, uid, vorwahl, nummer, login from kunden, gfs, kontakte, kdemail where 
$uidnummer (
kunden.kdnr like '$nummer' and 
kunden.vorname like '$vorname' and 
((kunden.nachname like '$nachname' or firmenname like '$nachname' or alteabholer like '$nachname') or
(gfs.nachname like '$nachname' and gfs.vorname like '$vorname' and kunden.kdnr = gfs.kdnr)) and 
(ort like '$ort' or plz like '$ort' or strasse like '$ort')) and
((vorwahl like '$telefon' or nummer like '$telefon') and kontakte.kunde = kunden.kdnr) or 
(email like '$telefon' and kontakte.kunde = kunden.kdnr)
and geloescht = '0' group by kunden.kdnr;

 

[BaseBallBatBoy]

Also hier mal meine Beobachtungen/Fragen:

• Dein SQL lässt sich so nur schlecht lesen. Formatiere das Query. Das hier kann helfen: http://www.dpriver.com/pp/sqlformat.htm
• Du musst unbedingt richtig joinen, nicht mit dem Where statement. Verwende stattdessen die INNER/LEFT/RIGHT JOIN Synatx
• Zudem, kannst du dann auf die Join Attribute einen Index/PK/FK legen, wenn du das nicht bereits gemacht hast
• Wozu das group by? Du hast keine Aggregatsfunktion im Select, ergo verwende auch kein group by
• Wenn man mehrere Tabellen abfragen will, ist es ratsam Tabellen Alisaes zu verwenden und jedes Attribut explizit mit dem korrekten Alias anzugeben. Erleichtert die Übersicht ungemein
• WHERE $uidnummer (.... schaut komisch aus. Was ist in der Variable drin und was willst du damit bezwecken/abfragen?
  

 

[Sprint]

Wenn ich JOIN jemals kapieren würde, würde ich es auch einsetzen. Hab mir das schon zig Mal angesehen, kapiere es aber einfach nicht.

Das GROUP ist drin, weil die Abfrage zum Teil mehrere gleiche Datensätze ausgegeben hat.

Alias hab ich probiert, verwirrt mich aber nur.

$uidnummer setzt eine Suche mit ein, wenn ein bestimmtes Feld in der Suchmaske gefüllt ist. Aus irgendeinem Grund hat die Suche nie funktioniert, wenn das Feld leer war

Bei einigen Teilen weiß ich aber selbst nicht mehr, warum was Bestimmtes gemacht wurde. Der Anfang der Suche stammt aus 2007 und ist immer wieder mal geändert und erweitert wurden.

 

[BaseBallBatBoy]

Wenn ich JOIN jemals kapieren würde, würde ich es auch einsetzen. Hab mir das schon zig Mal angesehen, kapiere es aber einfach nicht.

Mit Datenbanken effizient arbeiten zu wollen ohne Joins verstanden zu haben ist praktisch unmöglich. Joins sind das wichtigste Konzept bei Datenbanken. Daher empfehle ich dir sehr, das Konzept zu lernen, ohne wird es nicht gehen. Auf dem Internet findest du zig gute Erklärungen dazu. Z.B.: http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/
oder auch: http://www.sitepoint.com/understanding-sql-joins-mysql-database/

Kleiner Tipp, bei deiner Query müssten es wohl alles INNER JOIN sein

Das GROUP ist drin, weil die Abfrage zum Teil mehrere gleiche Datensätze ausgegeben hat.

Dazu sollte man group by nicht missbrauchen. Wenn du Duplikate entfernen willst verwende stattdessen DISTINCT

Alias hab ich probiert, verwirrt mich aber nur.

So kompliziert ist es nicht. Z.B. hast du momentan sowas: kunden.kdnr = gfs.kdnr
Das könnte dann so aussehen:

SELECT
k.vorname, k.nachname, .......
FROM kunden k
INNER JOIN gfs g
   ON k.kdnr = g.kdnr
....;

$uidnummer setzt eine Suche mit ein, wenn ein bestimmtes Feld in der Suchmaske gefüllt ist. Aus irgendeinem Grund hat die Suche nie funktioniert, wenn das Feld leer war

Ja schon klar. Aber was genau steht denn in $uidnummer drin? Kannst du mal zeigen wie deine Query aussieht, wenn alle Variablen abgefüllt sind?

 

[Sprint]

Das hier wäre ein ziemlich gefülltes Query, wobei es das aber nicht gibt. Meistens wird nur nach einem Begriff gesucht, der dann aber für mehrere Begriffe gilt.

SELECT kunden.kdnr,
risiko,
kunden.vorname,
kunden.nachname,
firmenname,
strasse,
hausnr,
plz,
ort,
gesperrt,
uid,
vortel1,
tel1,
login
FROM kunden,
gfs
WHERE uid LIKE '%50132%'
OR ( kunden.kdnr LIKE '%50132%'
AND kunden.vorname LIKE '%max%'
AND ( ( kunden.nachname LIKE '%mustermann%'
OR firmenname LIKE '%mustermann%'
OR alteabholer LIKE '%mustermann%' )
OR ( gfs.nachname LIKE '%mustermann%'
AND gfs.vorname LIKE '%max%'
AND kunden.kdnr = gfs.kdnr ) )
AND ( vortel1 LIKE '%987654%'
OR tel1 LIKE '%987654%'
OR vortel2 LIKE '%987654%'
OR tel2 LIKE '%987654%'
OR vortel3 LIKE '%987654%'
OR tel3 LIKE '%987654%'
OR vortel4 LIKE '%987654%'
OR tel4 LIKE '%987654%'
OR vortel5 LIKE '%987654%'
OR tel5 LIKE '%987654%'
OR vorfax1 LIKE '%987654%'
OR fax1 LIKE '%987654%'
OR vorfax2 LIKE '%987654%'
OR fax2 LIKE '%987654%'
OR vorfax3 LIKE '%987654%'
OR fax3 LIKE '%987654%'
OR vorfax4 LIKE '%987654%'
OR fax4 LIKE '%987654%'
OR vorfax5 LIKE '%987654%'
OR fax5 LIKE '%987654%'
OR vorhandy1 LIKE '%987654%'
OR handy1 LIKE '%987654%'
OR vorhandy2 LIKE '%987654%'
OR handy2 LIKE '%987654%'
OR vorhandy3 LIKE '%987654%'
OR handy3 LIKE '%987654%'
OR vorhandy4 LIKE '%987654%'
OR handy4 LIKE '%987654%'
OR vorhandy5 LIKE '%987654%'
OR handy5 LIKE '%987654%'
OR email1 LIKE '%987654%'
OR email2 LIKE '%987654%'
OR email3 LIKE '%987654%'
OR email4 LIKE '%987654%'
OR email5 LIKE '%987654%' )
AND ( ort LIKE '%Hamburg%'
OR plz LIKE '%Hamburg%'
OR strasse LIKE '%Hamburg%' ) )
AND geloescht = '0'
GROUP BY kunden.kdnr;

 

[BaseBallBatBoy]

Das hier wäre ein ziemlich gefülltes Query, wobei es das aber nicht gibt. Meistens wird nur nach einem Begriff gesucht, der dann aber für mehrere Begriffe gilt.

Das habe ich jetzt nicht verstanden. Das must du mir nun aber erklären. Wie meinst du das genau? Z.B. du suchst NUR nach "mustermann" oder NUR nach "Hamburg" order NUR nach "987654"?
Generell kann man sagen, dass das WHERE statement nur genau das beinhalten sollte was man auch wirklich gefiltert haben will.

Zudem bin ich jetzt etwas verwirrt. Dies ist doch die Query auf der alten Konfiguration oder? Ich dachte womit du Probleme hast ist die neue Variante mit mehreren Tabellen? Oder habe ich jetzt etwas falsch verstanden?
Wie auch immer.

Wenn ich mal deine zweite Query anschaue fällt auf, dass du keine Verbindung zu kdemail angegeben hast. MySQL wird also einen Kartesisches Produkt mit kdemail erstellen, eg. er joint jede einzelne Zeile aus den ersten drei Tabellen (kunden, gfs, kontakte) mit jeder Zeile aus kdemail. Das ist bestimmt nicht das ist was du willst. Dieser Fehler liesse sich einfach vermeiden wenn man die JOIN Syntax verwenden würde.

Ich mache dir mal ein kurzes Beispiel wie die Query aussehen könnte:

SELECT DISTINCT
       k.kdnr,
       k.risiko,
       k.vorname,
       k.nachname,
       k.firmenname,
       k.strasse,
       k.hausnr,
       k.plz,
       k.ort,
       k.gesperrt,
       k.uid,
       t.vorwahl,
       t.nummer,
       t.login
FROM kunden k
INNER JOIN gfs g
  ON k.kdnr = g.kdnr
INNER JOIN kontakte t
  ON k.kdnr = t.kunde
INNER JOIN kdemail e
  ON -- join condition musst du hier angeben
WHERE
    $uidnummer (
        k.kdnr LIKE '$nummer'
        AND k.vorname LIKE '$vorname'
        AND (
            (
                k.nachname LIKE '$nachname'
                OR k.firmenname LIKE '$nachname'
                OR k.alteabholer LIKE '$nachname'
            )
            OR (
                g.nachname LIKE '$nachname'
                AND g.vorname LIKE '$vorname'
            )
        )
        AND (
            k.ort LIKE '$ort'
            OR k.plz LIKE '$ort'
            OR k.strasse LIKE '$ort'
        )
    )
    AND (
        t.vorwahl LIKE '$telefon'
        OR t.nummer LIKE '$telefon'
    )
    OR (
        t.email LIKE '$telefon'
    )
    AND k.geloescht = '0';

Ich musste bei vielen Attributen raten aus welcher Tabelle sie stammen. Kann sein, dass ich bei einigen falsch geraten habe. Dann musst du halt z.B. k durch t ersetzen, wenn ein Feld nicht aus kunden sondern aus kontakte kommt.
Zudem fällt auf, dass AND und OR auf der selben Ebene verwendet werden. Sprich ein Match auf "OR (t.email LIKE '$telefon')" wird dir sofort ein Resultat bringen, egal ob z.B. "AND k.geloescht = '0'" gilt oder nicht. Ich vermute mal, das ist nicht was du wolltest...

Aus deiner Query vermute ich mal dass gilt: $uidnummer = "uid LIKE '%50132%' OR". Weiter oben hast du geschrieben "Aus irgendeinem Grund hat die Suche nie funktioniert, wenn das Feld leer war". Ich nehme dann mal an, dass selbst wenn du nicht nach uid suchen willst, wird deine Applikation folgendes abspeichern in $uidnummer = " OR"? Falls das stimmt, dann macht er ja am ende folgendes daraus: "WHERE OR ( kunden.kdnr LIKE '%50132%' ....." und da ist verständlich, dass die Query nie ohne funktionieren kann. Kein WHERE statement kann mit OR beginnen. Aber wie gesagt, das sind Vermutungen. Du der die Applikation kennst, kannst da wohl mehr Licht ins Dunkel bringen.

 

[Sprint]

Richtig, meistens wird nur nach dem Namen oder der Telefonnummer gesucht. Es sind aber Felder wie Vor-, Nach- und Firmenname zusammengefasst oder auch PLZ, Ort und Straße.

Auch richtig, das ist ein Query der alten Konfiguration. Die neue war aber auch nicht so viel anders.

Hm, dann könnte das der Grund sein, warum der DB Server sich jedes Mal verabschiedet hat.

Auch das andere vermutest du richtig. Wie gesagt, das ist in der Anfangszeit 2007 / 8 ein paar Mal erweitert und dann nie mehr angeschaut worden. Nur jetzt muß ich durch die Auslagerung der Kontaktdaten zwangsläufig das wieder anpacken.

Ok, dann werde ich mal versuchen, durch dein Beispiel durchzusteigen. Die von dir genannten Seiten haben schon mal etwas geholfen.

Bis hierhin schon mal danke für deine Hilfe.

 

[Sprint]

So, nachdem ich mit meiner eigentlichen Arbeit voll ausgelastet war, habe ich jetzt wieder etwas Zeit gefunden, hier weiter zu machen.

Ich habe das oben stehende Beispiel mal radikal gekürzt, um mich Schritt für Schritt da rein zu arbeiten. Mein erster Versuch war, die Suche Nach Vor- und Nachnamen über zwei Tabellen. Das sah dann so aus:

        SELECT DISTINCT
                    k.*
                FROM kunden k
                    LEFT OUTER JOIN gfs g
                        ON k.kdnr = g.kdnr
                WHERE

So weit so gut. Nun soll ja aber auch nach Vor- und / oder Nachnamen gefiltert werden. Die ersten beiden Varianten, also nur Vorname oder nur Nachname, funktionieren auch.

                     k.vorname LIKE '%willi%' OR g.vorname LIKE '%willi%'

                     k.nachname LIKE '%wuff%' OR g.nachname LIKE '%wuff%'

In dem Moment, in dem ein AND dazu kommt, geht es nicht mehr. Da kommen dann immer leere Resultate heraus

                   (k.vorname LIKE '%willi%' AND k.nachname LIKE '%wuff%')

                    (k.vorname LIKE '%willi%' AND k.nachname LIKE '%wuff%')
                          OR
                    (g.vorname LIKE '%willi%' AND g.nachname LIKE '%wuff%')