PHP-Scripte PHP-Tutorials PHP-Jobs und vieles mehr

PHP-Scripte PHP-Tutorials PHP-Jobs und vieles mehr (https://www.php-resource.de/forum/)
-   SQL / Datenbanken (https://www.php-resource.de/forum/sql-datenbanken/)
-   -   doppelte Datensätze mit unterschiedlicher WHERE-Bedingung? (https://www.php-resource.de/forum/sql-datenbanken/98761-doppelte-datensaetze-mit-unterschiedlicher-where-bedingung.html)

Dampfi 08-02-2010 17:26

doppelte Datensätze mit unterschiedlicher WHERE-Bedingung?
 
Hallo Community,

ich stehe gerade sowas von auf dem Schlauch und hoffe dass mir hier geholfen werden kann...

Folgendes Problem:

Auf meine Stammdaten-SQL-Tabelle mache ich folgende Abfrage:
Code:

SELECT count( * ) AS c, id FROM stammdaten GROUP BY concat( vorname, nachname, strasse, wohnort)
HAVING c = 2 ORDER BY ID ASC

Diese Abfrage liefert mir die IDs aller User, dessen VORNAME, NACHNAME, STRASSE und WOHNORT gleich sind und die exakt zweimal in der Tabelle stehen. (Bsp.: Max Mustermann auf der Musterstraße in Musterstadt steht einmal mit ID 11 und einmal mit ID 22 in der Datenbank... => Query gibt 11 und 22 zurück).

Das funktioniert auch perfekt, ist allerdings noch nicht mein Endziel. Jetzt weiß ich zwar, dass der User doppelt in der DB steht, aber das reicht mir noch nicht!
Und zwar habe ich in der Tabelle noch ein Feld "STATUS". Um am Beispiel zu bleiben: Die ID 11 hat im Feld STATUS eine "0" stehen, die ID 22 hat einen STATUS von "1". Aktuell ist das Resultat des Query IMMER 11 und 22, ganz egal welchen Status der doppelte eingetragene User besitzt.
Nun möchte ich aber nur die ID, deren STATUS = 0 ist, in dem Falle also NUR die ID 11. Das führt mich zu folgendem Problem:

Füge ich dem Query (siehe oben) ein WHERE STATUS = 0 bzw. 1 hinzu, so bekomme ich auch nur ein Result, wenn BEIDE Datensätze STATUS 0 bzw. 1 besitzen.
Ich möchte aber, dass ich nur dann ein Result bekomme, wenn der eine STATUS = 0 und der andere STATUS = 1 ist. Ist bei beiden Datensätzen des Users der STATUS gleich (also bei beiden 0 oder bei beiden 1), dann möchte ich KEIN Result haben...

Wie muss ich jetzt die SQL-Abfrage erweitern, um das zu realisieren...?
Ich hoffe ich habe mich verständlich ausgedrückt, es ist nicht ganz einfach zu beschreiben :(

Ich würde mich über Lösungsvorschläge freuen, vielleicht gibt es ja noch eine alternativen Ansatz...

PS: Die einfachste Lösung wäre natürlich alle IDs in ein ARRAY zu nehmen und dieses dann durch eine foreach-Schleife zu jagen, um den Status abzufragen. Allerdings handelt es sich um ca. 20k Datensätze, die Perfomance von SQL-Abfragen in solchen Schleifen ist aber so langsam, dass dies für mich nicht in Frage kommt! Ich brauche das Result, ohne eine foreach-Schleife anzustrengen - und das geht ja dann nur über die SQL-Syntax oder irre mich da?

AmicaNoctis 08-02-2010 17:40

Hallo,

dein Problem liegt in einem Missverständnis begründet. Deine Abfrage liefert dir nicht die IDs die doppelt sind, sondern jeweils eine beliebige ID aus einer Gruppe doppelter Datensätze.

Als Beispiel:

in der DB:
Code:

3, Max, Mustermann, Musterstadt
4, Berta, Beispiel, Irgendwo
5, Max, Mustermann, Musterstadt
6, jemand, anderes, nirgendwo
7, Berta, Beispiel, Irgendwo

im Abfrageergebnis:
Code:

2, 3
2, 7

oder

Code:

2, 5
2, 4

Welches davon du zurückbekommst, kannst du nicht vorab festlegen, aber ohne group_concat wählt MySQL eine beliebige ID aus der Gruppe aus und das kann beim Max die 3 oder die 5 und bei Berta die 4 oder die 7 sein.

Alle doppelten IDs pro Duplikatgruppe kannst du dir mit group_concat(id) in einer Spalte auflisten lassen.

Gruß,

Amica

Dampfi 08-02-2010 18:06

Zitat:

Zitat von AmicaNoctis (Beitrag 634731)
Hallo,

Als Beispiel:

in der DB:
Code:

3, Max, Mustermann, Musterstadt
4, Berta, Beispiel, Irgendwo
5, Max, Mustermann, Musterstadt
6, jemand, anderes, nirgendwo
7, Berta, Beispiel, Irgendwo

im Abfrageergebnis:
Code:

2, 3
2, 7


Danke erstmal für das schnelle Reply.
Allerdings liegt hier kein Missverständnis vor, denn ich möchte ja ALLE User erhalten, die doppelt da drin stehen. Also in deinem Beispiel ist das schon korrekt, dass ich einen Treffer auf Max Mustermann UND auf Berta Beispiel bekomme. So ist das schon gewollt.
Um mich an deinem Beispiel zu orientieren wünsche ich mir aber folgende Ausgabe:

in der DB:
Code:

3, Max, Mustermann, Musterstadt, 1
4, Berta, Beispiel, Irgendwo, 0
5, Max, Mustermann, Musterstadt, 0
6, jemand, anderes, nirgendwo, 1
7, Berta, Beispiel, Irgendwo, 0
(die angefügte Zahl ist mein Kriterium "Status")

im Abfrageergebnis:
Code:

2, 5
Mein Query findet die Dopplung, da VORNAME, NAME, STRASSE und ORT gleich sind. Wenn SQL einen solchen User findet, soll die Query gleich in einem Aufwasch mit prüfen, ob der Status gleich ist. Trifft dies zu, dann ist die Ausgabe unerwünscht. Ist bei beiden Datensätzen aber der Status verschieden (nämlich je ein betreffender Datensatz mit Status 0 und 1), dann wünsche ich eine Ausgabe der ID mit Status = 0...

Wie gesagt, vermutlich verfolge ich den falschen Ansatz, begriffen WARUM habe ich aber noch nicht... Oder bin ich hier wirklich dazu gezwungen eine Schleife zu erstellen und die IDs (die als Array vorliegen) da durchzujagen? Problem: Dann erreichen die User für ca. 3 Minuten die Seite nicht, denn solange läuft das Skript... Ist also nicht wirklich eine Lösung!

Danke aber schonmal dafür, dass du Dich mit dem Problem eines Fremden befasst :)

AmicaNoctis 08-02-2010 18:31

Zitat:

Zitat von Dampfi (Beitrag 634738)
Mein Query findet die Dopplung, da VORNAME, NAME, STRASSE und ORT gleich sind. Wenn SQL einen solchen User findet, soll die Query gleich in einem Aufwasch mit prüfen, ob der Status gleich ist. Trifft dies zu, dann ist die Ausgabe unerwünscht. Ist bei beiden Datensätzen aber der Status verschieden (nämlich je ein betreffender Datensatz mit Status 0 und 1), dann wünsche ich eine Ausgabe der ID mit Status = 0...

Da musst du noch einen Self Join machen, in dem du prüfst, ob der Status verschieden ist.

Dampfi 08-02-2010 18:43

Self Join? Noch nie gehört :D
Werde das heute im Feierabend (der JETZT beginnt) mal recherchieren und morgen früh dann ausprobieren. Danke erstmal für deine Hilfe, zumindest habe ich dank Dir eine Spur :)

Es würde mich freuen, wenn du morgen im Laufe des Tages nochmal in den Thread schaust, denn bei mir taucht bestimmt noch die ein oder andere Frage zum Self-Join in Verbindung mit meinem urspünglichen Problem auf ;)

Danke und sonnig-frostige Grüße aus Dresden
Dampfi

AmicaNoctis 08-02-2010 18:45

Ich schaue bestimmt nochmal rein.

Dieselben Grüße zurück, auch aus Dresden ;)

EVAMasters 08-02-2010 21:58

sowas kommt von sowas
 
Grundsätzlich gilt:

SELECT <Aggregat>, <Schlüssel>
FROM <Tabelle> GROUP BY <Schlüssel>

Mit anderen Worten: Die Id hat hier in der Tat nix zu suchen.


Probier folgendes:

Zunächst liefert uns die Selektion

SELECT <Schlüssel>
FROM stammdaten
GROUP BY <Schlüssel>
HAVING COUNT(*) = 2

alle Kandidaten, die 2x in der Tabelle vorkommen. Offenbar interessieren uns nur jene, die sich im <Status> unterscheiden, deren Summe über <Status> also gleich 1 ist. Right?

SELECT <Schlüssel>
FROM stammdaten
GROUP BY <Schlüssel>
HAVING COUNT(*) = 2 AND SUM(Status) = 1

Der gewünschte Schlüssel für den Join mit <stammdaten> (um an die tatsächliche Id dran zu kommen) ist demnach:

SELECT 0 AS Status, <Schlüssel>
FROM stammdaten
GROUP BY <Schlüssel>
HAVING COUNT(*) = 2 AND SUM(Status) = 1



d.

Dampfi 08-02-2010 23:59

Also ich habe die nötigen Dateien gerade nicht zur Hand, aber das erscheint mir logisch. Muss es natürlich ausprobieren, aber scheint als wäre das die schnellste und somit eleganteste Lösung für mein Problem...

Danke für dein Tutorial. Ich glaube ich sollte mich mehr mit SQL-Querys beschäftigen. Normal hol ich mir damit nur die Daten und regel den Rest über Arrays! :rtfm:

AmicaNoctis 09-02-2010 00:10

Zitat:

Zitat von EVAMasters (Beitrag 634748)
Code:

SELECT 0 AS Status, <Schlüssel>
  FROM stammdaten
  GROUP BY <Schlüssel>
  HAVING COUNT(*) = 2 AND SUM(Status) = 1


Zitat:

Zitat von Dampfi (Beitrag 634749)
aber das erscheint mir logisch

Mir nicht: select 0 as Status liefert immer 0, also wird die having-Bedingung sum(Status) = 1 nie erfüllt. Außerdem wollte Dampfi ja bei unterschiedlichen Statūs nur den Datensatz mit Status = 0. Ich behaupte immer noch, dass man dafür einen Self Join braucht.

Amica

EVAMasters 09-02-2010 00:46

SELECT 0 AS MyStatus

oder

einfach weglassen, was spielt das schon für eine Rolle für die Lösung?

tell me :rocks:

Bedenke: 0 oder 1 ist durch den <Schlüssel> nicht spezifiziert. Was aber gilt:
<Schlüssel> und 0 liefert eindeutig eine Id x
<Schlüssel> und 1 liefert eindeutig eine Id y

Im Umkehrschluss heißt das: Wir wählen den Status (0 oder 1) einfach (!) selbst. Damit haben wir den gewünschten Datensatz eindeutig identifiziert.

d.

AmicaNoctis 09-02-2010 00:55

Es spielt eine Rolle, weil kein Datensatz einer Gruppe zurückgegeben werden soll, wenn die gruppierten Datensätze den gleichen Status haben. Nur wenn er unterschiedlich ist, soll der Datensatz (oder dessen ID) zurückgegeben werden, der den Status 0 hat. Insofern kann man das eben nicht einfach selbst wählen, sondern muss erstmal die Datensätze mit der Übereinstimmung (<Schlüssel>) hinsichtlich ihrer Status-Felder vergleichen, um zu entscheiden, ob sie ins Ergebnis sollen und wenn ja, mit welchem Kandidaten.

Das lässt sich nun mal nicht mit group by realisieren, ohne eine Unterabfrage herzunehmen und da wäre es günstiger, mit einem Self Join zu arbeiten und ganz auf das group by zu verzichten.

Telling you! :readthis:

EVAMasters 09-02-2010 00:59

Zitat:

Zitat von Dampfi (Beitrag 634749)
Normal hol ich mir damit nur die Daten und regel den Rest über Arrays! :rtfm:

... ich weiß genau was du meinst ;)

Probleme kriegst du nur dann, wenn sich ein bestimmter Sachverhalt auch in PHP nicht ersichtlich oder nur schwer beschreiben lässt ...

Vielleicht erzähl ich ein wenig mehr zu diesem Thema auf dem <Usertreffen in Berlin>.

*join*

... probiers aus. Wenns nicht geht, geb ich dir nen Bier am 20sten aus, und ich werde die Angelegenheit nochmal überdenken.

Dampfi 09-02-2010 10:19

Sooo, ich habe mich heute morgen gleich voller Eifer ans Werk gemacht... und muss feststellen, dass EVAMasters Lösungs zwar logisch aussieht, mir als Resultat aber "LEER" z7urückgibt, und zwar in jeder Variation des Querys... :D

Ich muss zwar auf das Bier verzichten, da ich wohl nicht nach Berlin kommen werde, aber dem AmicaNoctis steht das doch viel eher zu ;)

Ich versuchs jetzt mal per Self-Join! :rocks:

EDIT:
Kommando (halb) zurück:
Code:

SELECT 0 AS Status, <Schlüssel>
  FROM stammdaten
  GROUP BY <Schlüssel>
  HAVING COUNT(*) = 2 AND SUM(Status) = 1

Funktioniert doch... Hatte nur etwas vergessen im Query anzupassen ^^
Jetzt bekomme ich ein Result, jedoch sind da nach Stichprobenentnahme IDs mit drin, die den Status=1 besitzen... -.-

Dampfi 09-02-2010 11:15

Die Endlösung, für die ich mich jetzt entschlossen habe:

Code:

SELECT STATUS , ID
FROM ma1_user_stammdaten
GROUP BY concat( vorname, nachname, strasse, wohnort )
HAVING COUNT( * )=2
AND SUM(STATUS)=1

Somit erhalt ich ein zweispaltiges Result in dieser Form:
Erster Datensatz:
[STATUS] = 0
[ID] = 234
Zweiter Datensatz:
[STATUS] = 1
[ID] = 257

Das Ganze frage ich mittels PHP ab, ob der Status 0 ist... Wenn ja => array_push. Dann habe ich alle User, die doppelt mit je einem Datensatz Status 0 bzw. 1 existieren abgegriffen und per PHP meine ID-Auswahl auf die Datensätze, die STATUS = 0 haben begrenzt...

Scheint gut zu funktionieren... Werde es dann gleichmal durch meine Testdatenbank jagen :)

Danke nochmal an alle Mitwirkenden für Eure Hilfe!:grin:

EVAMasters 09-02-2010 18:15

na, na, na - entäusch mich ma nicht mit deiner Lösung - kurz vor dem Ziel.

Es fehlt doch nur noch:

Zitat:

Zitat von EVAMasters (Beitrag 634748)
Der gewünschte Schlüssel für den Join mit <stammdaten> (um an die tatsächliche Id dran zu kommen) ist demnach:

look:

SELECT b.* FROM (SELECT <Schlüssel> FROM stammdaten GROUP BY <Schlüssel> HAVING COUNT(*) = 2 AND SUM(Status) = 1) a, stammdaten b
WHERE a.<Schlüssel> = b.<Schlüssel> AND b.Status = 0

that's it

d.

ps: Amica kriegt das Bier, falls ich meine Lösung überdenken müsste ;)


Alle Zeitangaben in WEZ +2. Es ist jetzt 21:21 Uhr.

Powered by vBulletin® Version 3.8.2 (Deutsch)
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Search Engine Friendly URLs by vBSEO 3.3.0
[c] ebiz-consult GmbH & Co. KG