Mehrfaches Vorkommen finden

[WernerK]

Hallo,
ich möchte in einer MySQL Tabelle "software" alle Seriennummen finden die mehrfach vorkommen.
Wenn ich einfach nach der Seriennummer suche finde ich 700 zeilen.

  
SELECT IdKd,beschreibung, SUBSTRING(maschserien, -6) AS SerienNummer From software  
WHERE maschserien != ""  
ORDER BY IdKd ASC  

Das "SUBSTRING(maschserien, -6)" mache ich weil die Seriennummer einmal 6 stellig nur mit Zahlen sein kann (200400) aber auch XACH200300.

Ich versuche nun mit HAVING und COUNT die gleichen Seriennummern zu filtern,

  
SELECT IdKd,beschreibung, SUBSTRING(maschserien, -6) AS SerienNummer From software  
WHERE maschserien != ""  
GROUP BY SUBSTRING(maschserien, -6)  
HAVING Count(maschserien) > 1  
ORDER BY IdKd ASC  

Jetzt werden aber nur noch 120 Einträge gefunden und das stimmt nicht denn einige Seriennummern werden nicht gelistet
Hat jemand eine Idee was daran falsch ist?

Gruss
Werner

[Ilja]

yo,

Das "SUBSTRING(maschserien, -6)" mache ich weil die Seriennummer einmal 6 stellig nur mit Zahlen sein kann (200400) aber auch XACH200300.

könnte man sich überlegen, das in zwei spalten aufzuteilen...

Jetzt werden aber nur noch 120 Einträge gefunden und das stimmt nicht denn einige Seriennummern werden nicht gelistet

zum einen funktioniert diese art der gruppierung nur bei mysql. du zeigst spalten an, über die du nicht gruppiert hast und keine aggregat-funktion sind. jedes andere dbms würde dir dafür eine fehlermeldung ausgeben.

zum anderen würde ich COUNT(*) > 1 verwenden, macht in dem falle mehr sinn. und du willst mit der abfrage ja die anzeigen, die eben doppelt vorkommen. mit der gruppierung fügst du eben datensätze mit der gleichen gruppierung zu einem datensatz zusammen. zusätzlich schließt du mit dem HAVING zusatz alle aus, die nicht doppelt vorkommen. ergo müssen es doch weniger werden oder habe ich jetzt was falsch interpretiert ?

Ilja

[WernerK]

Hi Ilja,

zum anderen würde ich COUNT(*) > 1 verwenden, macht in dem falle mehr sinn. und du willst mit der abfrage ja die anzeigen, die eben doppelt vorkommen. mit der gruppierung fügst du eben datensätze mit der gleichen gruppierung zu einem datensatz zusammen. zusätzlich schließt du mit dem HAVING zusatz alle aus, die nicht doppelt vorkommen. ergo müssen es doch weniger werden oder habe ich jetzt was falsch interpretiert ?

ja ich glaube du hast recht und ich habe falsch herum gedacht :-)
es werden ja nur die Einträge aufgelistet die mehrfach oder doppelt vorkommen.

Ich wollte eigentlich aus der Tabelle "software" alle Seriennummern auflisten die drin sind und die wo doppelt oder mehrfach vorkommen irgendwie gruppieren.
Hintergrund:
In der Tabelle "software" gibt es eine Spalte "IdKD" die auf eine Tabelle "Kunde" verweist.
Jetzt kann ein Kunde mehrere verschiedene Software Produkte haben die aber alle die gleiche Seriennummer haben. Ich wollte also etwas machen wie:

Kunde    Software     Seriennummer
------------------------------------------------
Meier,   Word,        245000
Meier,   Word97,      245000
Meier,   Word2000,    245000
Alber,   Excel,       230000
Müller,  Word,        300300

Gruss
Werner

[WernerK]

HAllo nochmals,

zum einen funktioniert diese art der gruppierung nur bei mysql. du zeigst spalten an, über die du nicht gruppiert hast und keine aggregat-funktion sind. jedes andere dbms würde dir dafür eine fehlermeldung ausgeben.

Das wusste ich noch nicht.
Wie müsste es denn korrekt sein, damit es auch bei anderen dbms funktioniert?
Es müsste doch möglich sein, dass man nach einer Spalte gruppiert, aber noch andere anzeigen lassen will oder?

Gruss
Werner

[Ilja]

yo,

Wie müsste es denn korrekt sein, damit es auch bei anderen dbms funktioniert?

bei anderen dbms musst du über die spalten gruppiert haben, um sie anzuzeigen oder aber aggregatfunktionen benutzen. allerdings kann man jetzt nicht wahllos alle spalten, die man anzeigen will in die gruppierung mit reinnehmen, da die gruppierung ja einen bestimmten zweck hat. das muss man von fall zu fall sehen.

Es müsste doch möglich sein, dass man nach einer Spalte gruppiert, aber noch andere anzeigen lassen will oder?

ich sage mal, es ist "fast alles" möglich. allerdings musst du dir erst mal genau im klaren sein, was du angezeigt haben willst. mit einer gruppierung will ich ja datensätze zusammenfassen, sprich in aller regel werden aus mehreren datensätze pro gruppe einer. dann kann ich zwar die spalten anzeigen, nach der ich gruppiert habe, aber bei den anderen spalten wird es schon schwieriger, da ich ja die spalteninhalte aus mehreren datensätze nehmen könnte, die in der gruppierung zusammengefasst wurden. mysql nimmt dann zum beispiel zufällig irgendeinen wert, andere dbms schmeissen darauf wie gsagt einen fehler, wenn du spalten anzeigen läßt, die nicht gruppiert sind. bei aggregat-funktionen kannst du das dann wieder machen, weil die eben einfach alle datensätze innerhalb einer gruppierung berücksichtigen.

ein stichwort für dich das problem zu lösen wären möglicherweise korrelierte unterabfragen. aber wie gesagt, man muss sie erst mal genau klar machen, welche daten habe ich und wie stelle ich mir das ergebnis vor. gibt doch dazu einfach mal beispieldaten an, die das gut darstellen.

Ilja

[WernerK]

Hallo Ilja,

danke für deine Erklärungen.

Mit folgender Abfrage bekomme ich nun alle Software Produkte die ein Kunde hat und zwar nur die wo es mehr als eine Bestellung gibt.

  
SELECT B.Kunde, A.IdKd,A.beschreibung, A.maschserien AS SerienNummer  
From  
software A  
LEFT JOIN bestellung B ON B.IdKd = A.IdKd  
WHERE SUBSTRING(A.maschserien, -6) IN  
(SELECT SUBSTRING(X.maschserien, -6)  
From  
software X  
WHERE X.maschserien != ""  
GROUP BY SUBSTRING(X.maschserien, -6)  
HAVING Count(*) > 1  
ORDER BY X.IdKd ASC)  
ORDER BY SUBSTRING(A.maschserien, -6) ASC, A.IdKd ASC  

Jetzt wäre es natürlich klasse, wenn man auch noch zusätzlich die Software Produkte anzeigen könnte die bei "software" nur einmal vorkommen, wo also die "maschserien" nur einmal vorkommt.
Geht das überhaupt in einer Abfrage?
Ansonsten müsste ich das halt im PHP Code als separate Abfrage machen.

viele Grüße
Werner

[Ilja]

yo,

Mit folgender Abfrage bekomme ich nun alle Software Produkte die ein Kunde hat und zwar nur die wo es mehr als eine Bestellung gibt.

viele wege führen nach rom, allerdings ist es immer besser, wenn man uns hier nicht die abfragen gibt, sondern eben die von mir geforderten beispieldaten und deine fachlichkeit, was genau du haben willst. in den abfragen der hilfesuchenden können eben schon fehler drinne sein und selbst wenn das nicht der fall ist, ist es mühsamer die abfrage zu analysieren, zumal ich von hause aus faul bin. viele hilfesuchenden verstehen nicht, warum es für uns viel wichtiger ist, die "rohdaten" zu bekommen, aber die sind einfach noch jungfräulich und somit einfacher für uns die lösung zu finden.

zum beispiel ist der LEFT JOIN bei deiner abfrage wohl nicht erforderlich, das ORDER BY X.IdKd ASC ist auch überflüssig, etc. wie dem auch sei, die abfrage ist nicht all zu schwierig, mir fehlt noch eine information. wenn es doppelte bestellung der gleichen software gibt für einen kunden, willst du dann alle doppelten datensätze anzeigen oder sie zusammenfassen und nur einen datensatz zurück geben ?

Jetzt wäre es natürlich klasse, wenn man auch noch zusätzlich die Software Produkte anzeigen könnte die bei "software" nur einmal vorkommen, wo also die "maschserien" nur einmal vorkommt.
Geht das überhaupt in einer Abfrage?

ja, das ist easy, wichtig ist wie oben vielmehr die infos, wie genau die ausgaben aussehen soll, dann ist es meistens recht einfach, dafür die abfrage zu machen.

Ilja

[WernerK]

Hallo Ilja,
danke nochmals für deine Hilfe.

ja, das ist easy, wichtig ist wie oben vielmehr die infos, wie genau die ausgaben aussehen soll, dann ist es meistens recht einfach, dafür die abfrage zu machen.

Nun, ich brauche im Prinzip eine Ausgabe wie:

Kunde    Software     Seriennummer
------------------------------------------------
Meier,   Word,        245000
Meier,   Word97,      245000
Meier,   Word2000,    245000
Alber,   Excel,       230000
Müller,  Word,        300300

In der Tabelle software gibt es pro Seriennummer mehrere Einträge
aber natürlich auch Einträge mit nur einer Seriennummer.
Mit der IdKD gibt es eine Relation zur Tabelle "bestellung" wo die Kundendaten drin stehen.
Ich möchte eine Aufstellung das man pro Kunde alle Software bzw. Einträge sieht. Mit meiner Gruppierung bekomme ich bisher ja nur die angezeigt, die mehr als 1 Eintrag in "software" haben.
Ich hoffe es ist nun ein wenig klarer?

Gruss
Werner

[Ilja]

yo,

Ich hoffe es ist nun ein wenig klarer?

ich gebe dir mal eine abfrage und du sagst mir, ob es passt und wen nicht, was noch fehlt.

SELECT b.Kunde, s.IdKd, s.beschreibung, s.maschserien SerienNummer,
       (SELECT COUNT(*)
        FROM sowftware s2
        WHERE s2.idkd = s.idkd
        AND SUBSTRING(s2.maschserien, -6) = SUBSTRING(s.maschserien, -6)
       ) Anzahl
FROM software s
INNER JOIN bestellung b ON b.IdKd = s.IdKd
ORDER BY s.IdKd, SUBSTRING(s.maschserien, -6)
;

damit zeigst du jeden datensatz der knuden an, der bestellt wurde. die anzahl mit der unterabfrage gibt jeweils an, wieoft die seriennummer des kunden bestellt wurde. alternativ könnte man auch die datensätze zusammenfassen, die den gleichen kunden haben und die gleiche seriennummer, dann würde sich die anzahl der datensätze reduzieren und mit hilfe der schönen mysql funktion group_concat könnte man alle die sofwatre ausgeben, die unter der gleichen seriennummer bestellt wurden.

Ilja

[WernerK]

Hallo Ilja,

ich gebe dir mal eine abfrage und du sagst mir, ob es passt und wen nicht, was noch fehlt.

Danke nochmals für deine Mühe.
Im Prinzip wird mir jetzt alles aus der Tabelle software aufgelistet bzw. angezeigt, so als ob ich
Select * from software machen würde.
Ich glaube das Problem ist das die IdKd für jede Bestellung anders ist.
Es kann also etwa so sein:
Tabelle "bestellung"

IdKD   Kunde   Kundennummer
100    Meier   77777
101    Müller  34449
102    Meier   77777
103    Kaiser  876555
104    Meier

Tabelle software
beschreibung       IdKD maschserien
Spiel Vollversion  100  200000
Spiel Vollversion  101  200001
Spiel Update 1     102  200000
Spiel Vollversion  103  200002
Spiel Update 2     104  200000

Ich weiss das das Design vermutlich schlecht war und ist. Es wäre z.b. wesentlich einfacher wenn es pro Kunde immer die gleiche Kundennummer geben würde. Manchmal ist das Feld aber auch ganz leer.
Ebenso wird der Kunde einmal Meier dann vielleicht wieder Meier GmbH geschrieben.
Ich kann also nur anhand der Seriennummer feststellen ob der Kunde schon mehrere Software bzw. Updates bekommen hat denn die "maschserien" bleibt immer gleich.

Ich schaue mal ob ich mit meinem Ansatz weiterkomme. Gegebenfalls muss ich halt zwei Abfragen machen. Ich möchte deine Zeit nicht zu sehr in Anspruch nehmen bzw. vielleicht geht es auch garnicht weil das Design der BD es nicht hergibt?

vielen Dank
Gruss
Werner

[Ilja]

yo,

Ich glaube das Problem ist das die IdKd für jede Bestellung anders ist.

hmm ok, idkd hörte sich ein wenig wie die id der kundennummer an, aber ist wohl die id jeder bestellung, was ich mir eigentlich hätte denken können bei dem join.

Ich kann also nur anhand der Seriennummer feststellen ob der Kunde schon mehrere Software bzw. Updates bekommen hat denn die "maschserien" bleibt immer gleich.

die seriennummer der spalte maschserien ist doch bezogen auf die software und nicht auf den kunden oder sehe ich das falsch ?

lass uns noch mal deine daten-struktur kurz anschauen. in der tabelle software stehen deine software-produkte, maschserien ist jetzt genau was, bzw. was ist die spalte idkd, ist das der primary key der tabelle software ? erkläre mir noch mal die einzelnen spalten im detail der beiden tabellen, besonders die primary keys und die foreign keys. da habe ich wohl noch nachholbedarf.

Ilja

[WernerK]

Hallo Ilja,
vielen Dank für deine Geduld.

lass uns noch mal deine daten-struktur kurz anschauen. in der tabelle software stehen deine software-produkte, maschserien ist jetzt genau was, bzw. was ist die spalte idkd, ist das der primary key der tabelle software ? erkläre mir noch mal die einzelnen spalten im detail der beiden tabellen, besonders die primary keys und die foreign keys. da habe ich wohl noch nachholbedarf.

Ja in der Tabelle software stehen die Software produkte mit Preis, Sachnummer und einer Seriennummer eben der Spalte "maschserien". Beim ersten bestellen eines Kunden wird immer die Vollversion bestellt. Dann bekommt das Produkt eine Seriennummer. Alle weiteren Bestellungen sind dann Updates auf eine neue Version. Die Seriennummer bleibt dann aber immer gleich. Die Spalte IdKd bildet eine Relation zur Tabelle "bestellung" wo die Kundendaten drin stehen.
Die SPalte IdKd ist der PrimaryKey (autoincrement) der Tabelle "bestellung"

Es kann halt sein das ein Kunde mal nur eine Vollversion bestellt und dann sind eben nur 1 x Eintrag in der Tabelle bestellung und software. Es gibt aber auch Kunden die im Laufe der Zeit immer wieder Updates machen. Dann gibt es praktisch mehrere Einträge in "bestellung" mit jeweils einer eindeutigen IdKd und immer einen dazugehörigen Eintrag in "software" auch mit der gleichen IdKd.
Ich wollte ja nun alle Bestellungen herausfinden die ein Kunde bestellt hat.

Gruss
Werner

[Ilja]

yo,

so, wochenende ist vorbei...

Beim ersten bestellen eines Kunden wird immer die Vollversion bestellt. Dann bekommt das Produkt eine Seriennummer. Die Seriennummer bleibt dann aber immer gleich.

so langsam taste ich mich an euer daten-design ran. verstehe ich das richtig, dass ein kunde in der ersten bestellung einen eintrag in der tabelle software bekommt und pro update dann immer einen zusätzlichen eintrag auch in der tabelle software als auch in der tabelle bestellung ?

übrigens, eine relation ist keine beziehung zwischen zwei tabellen, sondern eine relation ist eben eine tabelle im kontext von datenbanken. aber das verwechseln die meisten. in wikipedia steht es richtig, falls du nachschauen willst.

idkd ist dann wie von dir erwähnt der primary-key der tabelle bestellungen, der auch in der tabelle software als fremdschlüssel abgelegt wird. der name ist nicht sonderlich gut gewählt, dachte doch in meinen ersten anflug, das wäre der primarykey der kunden. wenn dem so ist, wird auch schon die "grausamkeit" eures daten-designs sichtbar, scheint sich um eine 1:1 beziehung zwischen software und bestellung zu handeln, letztlich ist es ja eine entität und bildet eine bestellung einer software ab. gab es gründe es in zwei tabellen aufzuteilen ?

je nachdem, wie genau du die daten dargestellt haben willst, gibt es mehrere möglichkeiten. ich gebe dir mal meine erste in leicht abgeändeter form wieder. was mir fehlt, ist die spaltenbezeichnung des primarykey der tabelle software, den musst du reinschreiben in die unterabfrage. was die abfrage macht ist ein einfacher join zwischen software und bestellnug, wobei ich davon ausgehen, dass es sich dabei um eine 1:1 beziehung handelt. die korrelierte unterabfrage ermittelt dann zu jeder software/bestellung die anzahl aus, indem es nach gleichen maschserien und kundennummern in der jweikligen tabelle sucht. was mich noch interessieren würde, wenn zwei verschiedene kunden das gleiche produkt bestellen, gibt es dann eine andere seriennummer in der tabelle software oder gleicht sie sich ?

SELECT b.Kunde, s.IdKd, s.beschreibung, s.maschserien SerienNummer,
       (SELECT COUNT(*)
        FROM software s2
        INNER JOIN bestellung b2 ON b2.IdKd = s2.IdKd
        WHERE SUBSTRING(s2.maschserien, -6) = SUBSTRING(s.maschserien, -6)
        AND b2.kundennummer = b.kundennummer
       ) Anzahl
FROM software s
INNER JOIN bestellung b ON b.IdKd = s.IdKd
ORDER BY b.kundennummer, SUBSTRING(s.maschserien, -6)
;

das gibt dir erst mal eine auflistung der gesamten software und als unterabfrage wie oft diese software bestellt wurde. schau erst mal, ob das ergebnis stimmt nud dann kann man sie noch nach deinen wünschen anpassen.

Ilja

[WernerK]

Hallo Ilja,

so langsam taste ich mich an euer daten-design ran. verstehe ich das richtig, dass ein kunde in der ersten bestellung einen eintrag in der tabelle software bekommt und pro update dann immer einen zusätzlichen eintrag auch in der tabelle software als auch in der tabelle bestellung ?

da es doch ein wenig umfangreicher ist von der Erklärung her habe ich es mal an deine Email gesendet.

viele Grüße

[Ilja]

yo Werner,

habe die e-mail gelesen, aber poste es doch noch mal hier, dann hat auch jeder die chance, es mitzulesen, falls es einen interessiert. wie ist den dein jetziger stand der dinge ?

Ilja