Zwei DB Abfragen an denen ich verzweifle (noobie)

[Jonas]

Hallo!

Unser Programmierer ist im wohlverdienten Urlaub und nicht erreichbar, also bleibt es am Prakti hängen - mir.

Ich kenne mich mit MySQL aber nur ganz rudimentär aus, daher hoffe ich auf Eure Hilfe bei zwei bestimmt recht simplen Aufgaben....

1. Doppelte Emails anzeigen
Wie muss ein Query aussehen, der alle Datensätze ausgibt, bei der die Email Adresse doppelt ist? Beispiel

1. Klaus Müller (klaus@müller.de)
2. Klaus F. Müller (klaus@müller.de)
3. Klaus Müller ([e-mail])

Gelistet werden sollen 1 und 2, weil ihre Email identisch ist (und es sich damit sehr wahrscheinlich um die gleiche Person handelt).

Kleine Schwierigkeit: das Feld "email" in der DB kann auch leer sein, diese Einträge sollten dann nicht mit angezeigt werden...

2. Datensätze mit hoher Übereinstimmung auflisten.
Wie kann ich alle Datensätze zeigen, bei denen mindestens, sagen wir, drei Übereinstimmungen existieren? Also z.B. Vorname, Nachname, Email sind identisch, Telefon und Wohnort z.B. unterschiedlich. Das alles sollte mir dann als Liste angezeigt werden.

Ich hoffe mich verständlich gemacht zu haben und danke Euch für Eure Hilfe sehr.

Grüße aus Stuttgart,
Jonas

[Rouven]

Hi,

1. Doppelte Emails anzeigen
   Wie muss ein Query aussehen, der alle Datensätze ausgibt, bei der die Email Adresse doppelt ist? Beispiel

Das ist noch eine relativ triviale Sache, du nimmst eine normale Abfrage SELECT email, fragst nur die Zeilen ab bei denen die email NOT NULL ist, lässt das Ergebnis nach gleicher Adresse gruppieren (GROUP BY) und wählst davon wiederum nur die, die mehr als einmal auftauchten (selbe Stelle im Manual; HAVING COUNT(*)...)

2. Datensätze mit hoher Übereinstimmung auflisten.
   Wie kann ich alle Datensätze zeigen, bei denen mindestens, sagen wir, drei Übereinstimmungen existieren? Also z.B. Vorname, Nachname, Email sind identisch, Telefon und Wohnort z.B. unterschiedlich. Das alles sollte mir dann als Liste angezeigt werden.

...da fällt mir im Moment leider nur eine manuelle Variante ein...

MfG
Rouven

[Jonas]

Hallo,

vielen Dank für Eure Antworten.
Was das simpel angeht... unser Programmierer hat sowas subjektiv empfunden meist in wenigen Minuten parat. Angesichts der vielen Profis hier habe ich es daher als simpel interpretiert. Für mich selbst ist das unvorstellbar.
Übrigens aus gleichem Grunde kann ich für mich aus den Hilfen kaum was ableiten, weil ich dafür noch zu wenig Ahnung habe. Naja, ich wollte doch Feuerwehrmann werden.

Ein Missverständnis muss ich jedoch beichten, hier war ich zu ungenau. Die Email Adressen stehen natürlich separat in der Tabelle, es gibt also Felder für z.B.

name
vorname
email
telefon
website
etc.

Wenn Ihr mir hier noch weiterhelfen könnt - vielen Dank.

Grüße,
Jonas

[Jonas]

Also, nur um mal meine Unkenntnis zu zeigen:

dies habe ich aus den Hinweisen gebaut (natürlich funktioniert es nicht)

SELECT * FROM 'contact_data' WHERE 'email' NOT NULL GROUP BY 'email' HAVING COUNT (*)

Danke,
Jonas

[dedlfix]

echo $begrüßung;

dies habe ich aus den Hinweisen gebaut (natürlich funktioniert es nicht)
SELECT * FROM 'contact_data' WHERE 'email' NOT NULL GROUP BY 'email' HAVING COUNT (*)

Dem NOT NULL fehlt vornan ein IS. Um auf NULL zu prüfen kann man nicht die üblichen Vergleichsoperatoren (= und <>) anwenden. Wenn einer der beiden Parameter NULL ist, kommt immer NULL raus, was auch einem false entspricht. Testet man also auf x = NULL, ergibt das auch bei NULL als Inhalt von x ein false. Deswegen gibt es die Extra-Syntax x IS NULL und x IS NOT NULL.

Ein COUNT(*) allein ergibt immer true, sobald das Ergebnis ungleich 0 ist. Möchtest du also Ergebniszeilen, bei Count-Ergebnissen von mehr als 1 solltest du das noch ergänzen.

echo "$verabschiedung $name";

[Rouven]

Hi,

und SELECT * und GROUP BY email lässt MySQL zwar durch, die Ergebnisse sind aber unvorhersehbar, man sollte auf SELECT email ausweichen. Andere DBMS werfen dir da auch einen Fehler raus.

MfG
Rouven

[Jonas]

Ich geb auf. Das klingt zwar alles plausibel, aber ich bekomme trotzdem kein Resultat. Mein Query ist jetzt

SELECT * FROM contact_data WHERE email IS NOT NULL GROUP BY email HAVING COUNT (2)

und dabei bekomme ich ein leeres Ergebnis (was falsch ist, es gibt hunderte doppelte Emails im Datenbestand). Ausserdem weiss ich nicht, wie ich beim COUNT angeben kann "mehr als 1".

Naja, ich will Eure Geduld nicht weiter strapazieren...

Vielen Dank,
Jonas

[Hans]

Hi !

1.)
select email, count(email) from tabelle group by email having count(email)=2;

2.)
select vorname||';'||nachname||';'||telefon, count(vorname||';'||nachname||';'||telefon) from tabelle group by vorname||';'||nachname||';'||telefon having count(vorname||';'||nachname||';'||telefon) =2;

Ich hoffe, deine mySQL-Version verträgt das alles ;-)

Gruß

Hans

[Rouven]

Hi,

select email, count(email) from tabelle group by email having count(email)=2;
2 bitte, ansonsten sind nur die echt-doppelten drin, nicht aber die dreifachen.

Deine Lösung zu (2) funktioniert nur, falls name+vorname+telefon die einzig relevante Ähnlichkeit ist, gilt aber auch name+vorname+stadt wird die Abfrage höllisch umfangreich.

MfG
Rouven

[Hans]

Hi !

2 bitte, ansonsten sind nur die echt-doppelten drin, nicht aber die dreifachen.

Jo, ich wußte nicht mehr, ob er jetzt exakt 2 oder mind. 2 haben wollte. Außerdem wollte ich die Ratespielchen abkürzen. Jonas schwitzt wahrscheinlich Blut und Wasser... ;-)

Deine Lösung zu (2) funktioniert nur, falls name+vorname+telefon die einzig relevante Ähnlichkeit ist, gilt aber auch name+vorname+stadt wird die Abfrage höllisch umfangreich.

Klar, wenn mal mit Umlaut, mal ohne, mal mit ß, mal mit "ss" geschrieben, kann man sich die Kugel geben. Ansonsten ggf. einen "upper" über die ganzen Strings machen, dann hat man schon mal die Groß-/Kleinschreibung eliminiert.

Gruß

Hans

[Jonas]

Hallo!

Ihr habt ja recht, ich habe mich zu leicht frustrieren lassen, das liegt, wie schon vermutet, daran, dass ich nur geringe Programmierkenntnisse habe, leider.

Der konkrete Query war natürlich super für mich, damit konnte ich die Dubletten recht schnell rausfiltern.

Was danach zwischen Euch diskutiert wurde muss ich mir mal in Ruhe durch den Kopf gehen lassen, das überstief spontan meine Aufnahmefähigkeit.

Euch allen vielen Dank,
Jonas (der jetzt nicht mehr soviel Blut und Wasser schwitzt)

[Hans]

Hi Jonas,

naja, jeder fängt mal klein an. Und: Du wolltest doch Feuerwehrmann werden, oder?

Kennst Du die Geschichte vom kleinen, grünen Drachen Grisu? Der wollte auch immer Feuerwehrmann werden. Aber jedesmal, wenn er sich so richtig gefreut hat, hat er vor lauter Freude Feuer gespien und aus war's mit dem Traum. Trotzdem hat er sich nicht entmutigen lassen und ich glaube, in einer Folge war er sogar mal Feuerwehrmann, äh, FeuerwehrDRACHE.

Gruß

Hans

[Alexander Brock]

Hallo Forum,

Deine Lösung zu (2) funktioniert nur, falls name+vorname+telefon die einzig relevante Ähnlichkeit ist, gilt aber auch name+vorname+stadt wird die Abfrage höllisch umfangreich.

Dann würde ich ein kleines Programm schreiben, das diese Abfrage(n) generiert *g*

Klar, wenn mal mit Umlaut, mal ohne, mal mit ß, mal mit "ss" geschrieben, kann man sich die Kugel geben. Ansonsten ggf. einen "upper" über die ganzen Strings machen, dann hat man schon mal die Groß-/Kleinschreibung eliminiert.

Man könnte versuchen, die Datenbank in eine andere Datenbank zu überführen, in der nicht mehr die originalen Daten sondern das Ergebnis von soundex() gespeichert ist.

Gruß
Alexander Brock

[Vinzenz Mai]

Hallo Alexander,

Deine Lösung zu (2) funktioniert nur, falls name+vorname+telefon die einzig relevante Ähnlichkeit ist, gilt aber auch name+vorname+stadt wird die Abfrage höllisch umfangreich.

Dann würde ich ein kleines Programm schreiben, das diese Abfrage(n) generiert *g*

so etwas kann richtig sein, aber hier?
Siehe mein Beispiel mit 3 Richtige aus 8 Möglichen:

[latex] {8 \choose 3} = 112[/latex] mögliche Kombinationen je dreier Vergleiche.

Das ist ganz bestimmt nicht der richtige Weg. Schreibe lieber eine Funktion, die die Zahl der Übereinstimmungen zurückliefert, das dürfte effizienter sein - von der Übersicht ganz zu schweigen.

Freundliche Grüße

Vinzenz

[Rouven]

Hi ihr,

mir kommt da heute noch eine ganz andere Variante. Was wäre, wenn man einfach zählt wieviele Übereinstimmungen denn da sind?

Schritt 1: Vinzenz Vorschlag mit dem Self-Join, damit wir wirklich zwei Datensätze vergleichen können.

Schritt 2: Eine Summe von IF-Anweisungen der Form:
SELECT
   linketabelle.id,
   rechtetabelle.id,
   (IF(linketabelle.name = rechtetabelle.name, 1, 0))
   +
   (IF(linketabelle.vorname = rechtetabelle.vorname, 1, 0))
   +
   ...
       AS matches
FROM tabelle AS linketabelle, tabelle AS rechtetabelle
WHERE linketabelle.id <> rechtetabelle.id
AND matches > 2

Dieses Konstrukt wächst auch nicht so schnell, weil es pro möglicher Übereintimmung nur ein Summand mehr wird.

MfG
Rouven

[Vinzenz Mai]

Hallo Rouven,

select email, count(email) from tabelle group by email having count(email)=2;
2 bitte, ansonsten sind nur die echt-doppelten drin, nicht aber die dreifachen.

1 bitte, ansonsten sind die echt doppelten auch nicht drin :-)

Deine Lösung zu (2) funktioniert nur, falls name+vorname+telefon die einzig relevante Ähnlichkeit ist, gilt aber auch name+vorname+stadt wird die Abfrage höllisch umfangreich.

Eben. (3 aus x) kann je nach Größe von x verdammt groß werden. Bei 3 Übereinstimmungen und 8 Spalten wären es bereits 112 Möglichkeiten, die zu betrachten sind :-)

Was ich mir vorstellen kann wäre in etwa folgendes:

Kreuzprodukt der Tabelle mit sich selbst.
eingeschränkt mittels einer Funktion, die die Übereinstimmungen in der Liste der relevanten Spalten zählt und deren Rückgabewert mindestens 3 ist :-)
Ich kann mir nicht vorstellen, dass das performant ist - aber besseres fällt mir auch nicht ein.

Freundliche Grüße

Vinzenz

[Vinzenz Mai]

Hallo Jonas,

Ich geb auf.

Toll! Welches Durchhaltevermögen! Meinst Du, uns wäre unser Wissen von allein zugeflogen? Meinst Du, wir hätten gleich aufgegeben? Was meinst Du, wieoft ich mir die Lösung für ein Problem erst am nächsten Tag oder gar in der nächsten Woche erarbeiten konnte.

Das klingt zwar alles plausibel, aber ich bekomme trotzdem kein Resultat. Mein Query ist jetzt

SELECT * FROM contact_data WHERE email IS NOT NULL GROUP BY email HAVING COUNT (2)

Rouven hat Dich doch extra darauf hingewiesen, dass Du nicht * verwenden sollst. Ersetze * durch email!

und dabei bekomme ich ein leeres Ergebnis (was falsch ist, es gibt hunderte doppelte Emails im Datenbestand). Ausserdem weiss ich nicht, wie ich beim COUNT angeben kann "mehr als 1".

Verwende für Vergleiche Vergleichsoperatoren!  Diesen Handbuchabschnitt hatte ich Dir bereits verlinkt. Wenn Du mit Englisch nicht zurecht kommst, solltest Du dies erwähnen; es gibt auch eine (meist nicht so aktuelle und vollständige) deutsche Handbuchversion.

Eine Frage am Rande: Wieviel Erfahrung im Programmieren hast Du? Völlig gleichgültig welche Sprache.

Naja, ich will Eure Geduld nicht weiter strapazieren...

Wer wird denn gleich die Flinte ins Korn werfen? Wir haben viel Geduld ...

Freundliche Grüße

Vinzenz

[Vinzenz Mai]

Hallo Jonas,

Was das simpel angeht... unser Programmierer hat sowas subjektiv empfunden meist in wenigen Minuten parat.

ja und? Für die Abfrage für Dein erstes Problem benötige ich auch keine zwei Minuten, dennoch war sie nach Deiner Ausgangsfragestellung für mich nicht simpel, da die E-Mail-Adresse aus einer Zeichenkette extrahiert werden muss. OK, da hast Du Dein Problem nicht richtig rübergebracht.

Angesichts der vielen Profis hier habe ich es daher als simpel interpretiert. Für mich selbst ist das unvorstellbar.

Nein, auch wenn etwas für einen mit einigermaßen viel Praxis in wenigen Minuten erledigt ist, ist es deswegen noch lange nicht simpel. Eine Abfrage, die ein GROUP BY, ein COUNT() und ein HAVING enthält, ist nicht mehr simpel. Das ist meine persönliche Ansicht - und ich weiß durchaus, wovon ich rede.

Übrigens aus gleichem Grunde kann ich für mich aus den Hilfen kaum was ableiten, weil ich dafür noch zu wenig Ahnung habe.

Du schriebst:

Ich kenne mich mit MySQL aber nur ganz rudimentär aus

was für mich bedeutet, dass Du zumindest einfache SELECT-Anweisungen hinbekommst. Ich habe Dir die relevanten Handbuchseiten verlinkt, Dir wurden mehrfach die relevanten SQL-Schlüsselworte genannt. Lesen solltest Du schon selbst.

Aufgabe 2 ist nicht trivial und die Lösung garantiert eine heftige Belastung für den Datenbankserver. Merke: Je unschärfer die Aufgabenstellung, um so höher typischerweise der Aufwand.

Wie gehst Du von Hand an eine solche Aufgabe heran:
Du nimmst gehst der Reihe nach alle Datensätze durch
Für jeden Datensatz überprüfst Du _alle anderen_ Datensätze, ob sie Deinem Kriterium entsprechen. Die Datensätze, für die es passt, kommen ins Töpfchen, die anderen ins Kröpfchen :-)

Freundliche Grüße

Vinzenz

[Vinzenz Mai]

Hallo Jonas,

Ich kenne mich mit MySQL aber nur ganz rudimentär aus, daher hoffe ich auf Eure Hilfe bei zwei bestimmt recht simplen Aufgaben....

warum gehst Du davon aus, dass Deine Aufgaben "recht simpel" sind? Wie definierst Du denn simpel? Für mich bedeutet simpel "einfach". Beide Aufgaben sind meiner Meinung nach _nicht_ einfach.

1. Doppelte Emails anzeigen
   Wie muss ein Query aussehen, der alle Datensätze ausgibt, bei der die Email Adresse doppelt ist? Beispiel

2. Klaus Müller (klaus@müller.de)

3. Klaus F. Müller (klaus@müller.de)

4. Klaus Müller ([e-mail])

Stehen die Datensätze genauso in der Datenbank. Grundsätzlich mit der E-Mail-Adresse in runden Klammern? Wirklich? Warum?

Gibt es sonst noch etwas, was in runden Klammern steht?
Wenn nein, dann

1. Wähle nur den Inhalt, der zwischen den runden Klammern steht
   LOCATE() und SUBSTR() könnten Dir dabei helfen. Beide findest Du
   bei den Zeichenkettenfunktionen

2. Gruppiere nach dieser berechneten Spalte und zähle die Einträge
   Lass Dir nur die anzeigen, die mehr als einmal vorkommen.
   GROUP BY, COUNT und HAVING, Infos dazu im Abschnitt Functions and Modifiers for Use with GROUP BY Clauses.

Kleine Schwierigkeit: das Feld "email" in der DB kann auch leer sein, diese Einträge sollten dann nicht mit angezeigt werden...

Dies ist nun wirklich eine simple Bedingung. Felder, in denen _nichts_ steht, enthalten den speziellen Wert NULL. Auf diesen prüfst Du mit
IS NULL bzw. IS NOT NULL, siehe Vergleichsoperatoren. Diese Einschränkung gehört übrigens in die WHERE-Klausel.

2. Datensätze mit hoher Übereinstimmung auflisten.
   Wie kann ich alle Datensätze zeigen, bei denen mindestens, sagen wir, drei Übereinstimmungen existieren? Also z.B. Vorname, Nachname, Email sind identisch, Telefon und Wohnort z.B. unterschiedlich.

Präzisiere bitte diese Anforderung. Diese Aufgabe ist nicht trivial. Gib bitte außerdem an, was Dir zur Verfügung steht. Die Leistungsfähigkeit von MySQL unterscheidet sich von Version zu Version enorm. Ohne Kenntnis der Dir zur Verfügung stehenden Version kann man Dir nicht sinnvoll helfen.

Das alles sollte mir dann als Liste angezeigt werden.

Das ist bei Datenbankabfragen normal.

Freundliche Grüße

Vinzenz