eine spalte aber unterschiedliche abfragewerte

[carsten_87]

Hi Leute,

ich arbeite nun schon länger mit mysql. Heute steh ich nun das erste mal aufn schlauch und komme echt nicht mehr weiter.
Ich habe folgendes Problem.

Vorher die Tabelle mit ein paar beispiel Datensätzen:

CREATE TABLE IF NOT EXISTS `kunden` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3;

CREATE TABLE IF NOT EXISTS `kunden_verteiler` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `kunden_id` int(11) NOT NULL,
  `verteiler_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6;

INSERT INTO `kunden` (`id`, `name`) VALUES
(1, 'Test'),
(2, 'Test2');

INSERT INTO `kunden_verteiler` (`id`, `kunden_id`, `verteiler_id`) VALUES
(1, 1, 2),
(2, 1, 6),
(3, 1, 9),
(4, 2, 2),
(5, 2, 6);

Ich habe eine Tabelle, in dieser gibt es 3 Spalten(id, kunden_id, verteiler_id)
Die id ist der primary key, kunden_id = fremdschlüssel von der Kundentabelle und Verteiler_id = Fremdschlüssel von der Verteilertabelle.
Ein Kunde kann mehrere Verteiler haben.
So hat hier der Kunde mit der id 1 insgesamt 3 Verteiler...2,6 und 9.
Und Kunde mit der id 2 hat 2 Verteiler...2 und 6

Nun möchte ich mit hilfe der SQL Abfrage auslesen welche Kunden alle Verteiler 2 UND 6 UND 9 haben. Sprich in diesem Beispiel darf Kunde 2 nicht mit angezeigt werden.

Mein erster lösungsansatz war dieser

SELECT * FROM kunden
LEFT JOIN kunden_verteiler ON kunden_verteiler.kunden_id = kunden.id
WHERE verteiler_id = 2 AND verteiler_id = 6 AND kunden_verteiler.verteiler_id = 9

bis mir einfiel, das es so gar nicht geht, da er jetzt in verteiler_id 2,6 und 9 in einer Spalte erwartet. Diese aber natürlich auf mehrere Datensätze aufgeteilt sind.
Mein Zweiter Lösungsansatz war dann dieser

SELECT * FROM kunden 
LEFT JOIN kunden_verteiler ON kunden_id = kunden.id
WHERE verteiler_id = 2 AND (SELECT count(id) as co_tmp FROM kunden_verteiler WHERE verteiler_id = 6) AND (SELECT count(id) as co_tmp FROM kunden_verteiler WHERE verteiler_id = 9)

Das sieht schon besser aus, jedoch kommt bei dieser Abfrage auch die Kundennummer 2 mit. Obwohl der gar nicht verteiler_id 9 hat.

Habe dann die Zweite SQL Abfrage noch etwas modifiziert

SELECT * FROM kunden 
LEFT JOIN kunden_verteiler ON kunden_id = kunden.id
WHERE (SELECT count(id) as co_tmp FROM kunden_verteiler WHERE verteiler_id = 2) AND (SELECT count(id) as co_tmp FROM kunden_verteiler WHERE verteiler_id = 6) AND (SELECT count(id) as co_tmp FROM kunden_verteiler WHERE verteiler_id = 9)

SELECT * FROM kunden 
LEFT JOIN kunden_verteiler ON kunden_id = kunden.id
WHERE verteiler_id = 2 AND (SELECT count(id) as co_tmp FROM kunden_verteiler WHERE verteiler_id = 6 AND (SELECT count(id) as co_tmp FROM kunden_verteiler WHERE verteiler_id = 9))

aber mit dem selben ergebnis, das beide Kunden angezeigt werden.

Funktioniert alles aber nicht. Entweder er zeigt alle Datensätze an, oder gar nichts.

Ich hoffe ihr könnt mir helfen.

Ziel soll später sein, das man mehrere Verteiler auswählen kann, und nur Kunden die in verteiler 1 UND 2 UND 3 etc.. stehen werden angeschrieben o.ä.

[dibo33]

Mittels IN nach den 3 Verteiler_Ids suchen und mittels HAVING die Anzahl überprüfen
[highlight=sql]SELECT k.id, k.name
FROM kunden k
JOIN kunden_verteiler kv
ON k.id = kv.kunden_id
WHERE kv.verteiler_id IN (2, 6, 9)
GROUP BY k.id, k.name
HAVING COUNT(kv.verteiler_id) = 3
[/highlight]

[carsten_87]

ah klasse.

Mit IN hatte ich es auch kurz probiert, aber das prüfen der Anzahl ganz außer acht gelassen. Vielen Dank für die Hilfe


Edit:
Wie sieht es bei dem Group By aus. Muss ich dort alle Spalten eintragen welche ich im SELECT angegeben habe? Wenn ichs weglasse kommen jedenfals keine Ergebnisse. ALso muss es rein. Nur die frage was ich alles eintragen MUSS

[dibo33]

Ins GROUP BY kommen alle Felder aus dem SELECT welche nicht einer Aggregat-Funktion entstammen (wobei MySQL hier sehr großzügig ist).

Edit: Wenn also durch die kunden.id eindeutige Gruppierungen möglich sind genügt diese bei MySQL.

Du solltest nur keine Felder aus kunden_verteiler selektieren, da sonst der Vergleich mit der Anzahl nicht hinhaut.

[carsten_87]

Danke für die Erklärung. Dann reicht die id, da diese den datensatz eindeutig macht.
Nope kunden_verteiler benötige ich hier nur um rauszufinden welcher kunde in welchen verteilern steckt, damit dieser entsprechende e-mails bekommt.

[Falk Prüfer]

Hallo,

...Wenn also durch die kunden.id eindeutige Gruppierungen möglich sind genügt diese bei MySQL....

Dann genügt diese nicht nur bei MySQL, sondern auch dem SQL-Standard! Alle Felder aus dem Select die aggregiert bzw. funktional abhängig sind, müssen nicht in der Group By-Klausel aufgeführt werden.
Diese Festlegung des SQL-Standards wird häufig verkannt, da es wohl noch kein DBMS gibt, welches das vernünftig implementiert. Die funktionale Abhängigkeit zu erkennen wird einfach schwierig sein, so dass die meisten DBMS einfach alle Felder aus dem Select im Group By erwarten, was dem Standard von vor 1992! entspricht. Nur MySQL geht hier den anderen Weg - schießt aber über das Ziel hinaus und überlässt dem SQL-Programmierer die Entscheidung was funktional abhängig ist und was nicht.

Nur die k.id im Group By aufzuführen wäre also nicht nur möglich, sondern nach SQL-Standard sogar RICHTIG! (Ist so jedoch nur unter MySQL lauffähig)

Gruß Falk

[carsten_87]

Hi Leute,

ich muss mal meinen alten Thread wiederbeleben.
Also die Lösung die ihr mir vor einiger Zeit gegeben habt funktioniert wunderbar. Nur habe ich jetzt leider die Problematik das zum Kunden nun auch die Kontaktvarianten hinzukamen, heißt wo hat man den Kunden kennengelernt.
Jetzt habe ich 3 Tabellen:
"kunden"
"kunden_verteiler"
"kunden_kontaktvarianten"

Ich nehme von unten jetzt nochmal die Tabellen als Beispiel

CREATE TABLE IF NOT EXISTS `kunden` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3;

CREATE TABLE IF NOT EXISTS `kunden_verteiler` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `kunden_id` int(11) NOT NULL,
  `verteiler_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6;

CREATE TABLE IF NOT EXISTS `kunden_kontaktvarianten` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `kunden_id` int(11) NOT NULL,
  `kontaktvarianten_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6;

INSERT INTO `kunden` (`id`, `name`) VALUES
(1, 'Test'),
(2, 'Test2');

INSERT INTO `kunden_verteiler` (`id`, `kunden_id`, `verteiler_id`) VALUES
(1, 1, 2),
(2, 1, 6),
(3, 1, 9),
(4, 2, 2),
(5, 2, 6);

INSERT INTO `kunden_kontaktvarianten` (`id`, `kunden_id`, `kontaktvarianten_id`) VALUES
(1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 2, 1),
(5, 2, 2);

Nun möchte ich gerne das außerdem zu den verteiler auch die Kontaktvariante mit ausgewertet wird.
Hier im Beispiel hätte ich nun also gerne das nur kunden erscheinen welche verteiler 2, 6 und 9 und als kontaktvariante 1,2 und 3 haben.
Habe jetzt versucht das having dahingegehend und das IN zu erweitern indem ich ein AND mit reingebracht habe, allerdings findet er dann gar nichts?

SELECT k.id, k.name
FROM kunden k
JOIN kunden_verteiler kv ON k.id = kv.kunden_id
JOIN kunden_kontaktvarianten kk ON k.id = kk.kunden_id
WHERE kv.verteiler_id IN (2,6,9) AND kk.kontaktvarianten_id IN (1,2,3)
GROUP BY k.id
HAVING COUNT(kv.verteiler_id) = 1 AND COUNT(kk.kontaktvarianten_id) = 3

Wo liegt der Fehler?

[Michael Bieri]

Hmm, so spontan gesehen wäre hier wohl ein count(distinct [ATTRIBUT]) gefragt, dann hast du die Anzahl eindeutige Keys, statt die Anzahl aller Keys.

Und wenn du schon prüfen willst ob er alle 3 Verteiler hat - dann solltest du das auch tun (anstatt nur einer)


Sowas müsste dann klappen:

.. HAVING COUNT(distinct kv.verteiler_id) = 3 AND COUNT(distinct kk.kontaktvarianten_id) = 3

[dibo33]

Warum COUNT(kv.verteiler_id) = 1 ?

Nimm einfach mal die HAVING-Klausel aus dem SELECT und wähle zusätzlich COUNT(kv.verteiler_id) und COUNT(kk.kontaktvarianten_id) um zu sehen was du an Datensätze bekommst.

Damit stellst du fest das du weder mit COUNT(kv.verteiler_id) = 1 noch mit COUNT(kk.kontaktvarianten_id) = 3 zu einem vernünftigen Ergebnis kommst.

Setze DISTINCT ein um die Ids entsprechend nur einmal zu zählen, desweiteren willst du ja 3 kv.verteiler_id haben und nicht nur eine also lautet dein HAVING

HAVING COUNT(DISTINCT kv.verteiler_id) = 3 AND COUNT(DISTINCT kk.kontaktvarianten_id) = 3

Edit: uups da war schon wer.

[carsten_87]

neuer tag neues glück?...
..anscheinend...

Danke für die schnelle Antwort. Fehlte ja eigentlich nur das distinct. OK der Fehler mit der 1 war eher ausversehen, hab vorher die Abfrage nochmal umgeändert gehabt und hab dann teil für teil rauskopiert und dabei die 1 vergessen zurück zu einer 3 zu ändern.

Wollte erst noch fragen warum das distinct da reinmuss beim count, aber mitlerweile kam mein Gehirn auch auf trab und ich konnte es mir selber erklären.(Wenn ich mir das count mit anzeige stehen da ja 12, habe mich erst gewundert, aber er zählt zu jedem Kunden Alle Einträge in der Datenbank, 5 Einträge * 2 Kunden + 2 doppelte IDS = 12)

SELECT k.id, k.name
FROM kunden k
JOIN kunden_verteiler kv ON k.id = kv.kunden_id
JOIN kunden_kontaktvarianten kk ON k.id = kk.kunden_id
WHERE kv.verteiler_id IN (2,6,9) AND kk.kontaktvarianten_id IN (1,2,3)
GROUP BY k.id
HAVING COUNT(distinct kv.verteiler_id) = 3 AND COUNT(distinct kk.kontaktvarianten_id) = 3

Vielen Dank für die schnelle professionelle Hilfe :-)

[carsten_87]

Da bin ich wieder

also es klappt bis jetzt alles wunderbar, nur ist leider demjenigen der die Kundenverwaltung nutzt noch ein kleiner Fehler aufgefallen der logisch mir nicht so ganz in den Sinn kommt. Also ich habe mal ein Bild der Kundenverwaltung erstellt, damit ihr seht wie man diese nutzt. Im endeffekt wird wenn man dann auf export klickt jede Gruppe durchgegangen(die Werte in einer Gruppe werden mit einem UND verknüpft). Jede Gruppe steht für sich und wird mit einem OR zur anderen getrennt.

Nun zu dem kleinen Problem.
Ich beziehe das beispiel jetzt auf das Bild
Ich habe 2 Gruppen. Gruppe 1 hat 2011 und Gruppe 2 hat 2012.
Nun möchte ich also alle Kunden haben die als Verteiler 2011 ODER 2012 haben.
(Nur so nebenbei, wenn beide in einer Gruppe wären würde ich ja sagen gebe mir alle Kunden die Verteiler 2011 UND 2012 haben)

Erst mal lesen wir jede Gruppe sondiert aus:
Gruppe 1(2011) insgesamt 939 Einträge
Gruppe 2(2012) insgesamt 325 Einträge

Wenn ich nun beide Gruppen miteinander Verbinde, kommen plötzlich nurnoch 640 Einträge bei raus(eigentlich müsste doch mindestens 939 sein, da ja bei Gruppe 1 die meisten sind und selbst wenn doppelte Einträge wären, die 939 nicht unterschritten werden.).

Kurze Erklärung zu den Tabellen:
admin_kunden_firma->kf alle Firmeninformationen
admin_kunden_ansprechpartner->ka Alle Ansprechpartner, über einen Fremdschlüssel wird die Firma angegeben
admin_kunden_verteiler->av Ansprechpartner ID und Verteiler ID werden hier eingetragen

[highlight=sql]
SELECT DISTINCT
kf.firma,
kf.firma2,
kf.kundennummer,
kf.erstell_datum,
kf.mitarbeiter,
ka.*
FROM admin_kunden_firma kf
LEFT JOIN admin_kunden_ansprechpartner ka ON kf.kundennummer = ka.kundennummer
LEFT JOIN admin_kunden_verteiler av ON av.kunden_id = ka.id
WHERE
(
(
av.verteiler_id IN (33)
)
OR
(
av.verteiler_id IN (38)
)
)
GROUP BY ka.id
HAVING COUNT(distinct av.verteiler_id) = 1 OR
COUNT(distinct av.verteiler_id) = 1
ORDER BY kf.kundennummer
[/highlight]

Wo sind meine restlichen 299(oder mehr) Einträge hin?

[Falk Prüfer]

Hallo,

...Nun möchte ich also alle Kunden haben die als Verteiler 2011 ODER 2012

Auch wenn dein Statement komplizerter aussieht, dass was du abfragst sind die, die entweder in 2011 oder in 2012 einen Eintrag haben. Durch das "HAVING COUNT(distinct av.verteiler_id) = 1" wird es zum EXCLUSIVE OR.

Aber...

...
[highlight=sql]
...
WHERE
(
(
av.verteiler_id IN (33)
)
OR
(
av.verteiler_id IN (38)
)
)
...
[/highlight]

Das ist das Gleiche wie:
[highlight=sql]
...
WHERE
av.verteiler_id IN (33, 38)
...
[/highlight]
Da kannst du noch soviele Klammern setzen, ein IN bleibt ein OR bleibt ein OR...

und...

...
[highlight=sql]
...
HAVING COUNT(distinct av.verteiler_id) = 1 OR
COUNT(distinct av.verteiler_id) = 1
...
[/highlight]

ist einfach nur:
[highlight=sql]
...
HAVING COUNT(distinct av.verteiler_id) = 1
...
[/highlight]
Eine Bedingung verändert sich nicht indem man sie mehrfach mit sich selbst OR-verknüpft...

...Nur so nebenbei, wenn beide in einer Gruppe wären würde ich ja sagen gebe mir alle Kunden die Verteiler 2011 UND 2012 haben

Wenn dich dein Gefühl nicht täuscht und es tatsächlich das ist, was du haben möchtest, dann muß das HAVING COUNT einfach nur mit 2 (statt 1) verglichen werden.

[highlight=sql]
...
WHERE
av.verteiler_id IN (33, 38)
GROUP BY ka.id
HAVING COUNT(DISTINCT av.verteiler_id) = 2
[/highlight]

Gruß Falk

[carsten_87]

Hi Falk,
danke für die info.
Naja, die klammern mache ich aus gewohnheit. Der Teil funktioniert ja soweit, das heißt ja im endeffekt das ich kunden haben möchte die 2011 UND 2012 haben. Allerdings möchte ich ja Kunden haben die 2011 ODER 2012 haben. Und dafür ist ja der Teil der Abfrage falsch

[highlight=sql]
WHERE
av.verteiler_id IN (33, 38)
GROUP BY ka.id
HAVING COUNT(DISTINCT av.verteiler_id) = 2
[/highlight]

Hmm, also mir viel gerade ein, was wäre wenn ich beim having count anstelle
HAVING COUNT(DISTINCT av.verteiler_id) = 2
ein
HAVING COUNT(DISTINCT av.verteiler_id) >= 1
mache?

meine verschollenen Einträge sind jedenfalls da(960).

allerdings wenn man
Gruppe 1
2011
2012
Gruppe 2
2013
hat, funktioniert es dann schon wieder nicht.
Alle Kunden die 2011 UND 2012 ODER 2013 haben

Da er ja beim having count nur guckt ob die anzahl größer gleich ist, nicht aber ob 2011 UND 2012 zusammen eingetragen sind.(kunde kann ja 2011 und 2013 als verteiler haben, aber da 2012 fehlt fällt er raus. Bei meiner Überlegung jetzt aber nicht da wie gesagt nur geprüft wird ob die anzahl richtig ist).

Argh, alles so kompliziert. Und je mehr man überlegt, desto mehr hindernisse fallen einen auf.

[frauwue]

Hi,

dann musst Du es halt mit WHERE EXISTS lösen.
Beispiele findest Du hier im Forum genug.

Gruß

[Falk Prüfer]

....Hmm, also mir viel gerade ein, was wäre wenn ich beim having count anstelle
HAVING COUNT(DISTINCT av.verteiler_id) = 2
ein
HAVING COUNT(DISTINCT av.verteiler_id) >= 1
mache?

Dann kannst du es weglassen! COUNT(DISTINCT av.verteiler_id) ist bei deiner Abfrage (...av.verteiler_id IN (33, 38) ...) in JEDEM FALL >= 1.
Du solltest aufhören zusammenhanglos Dinge zu probieren, sondern versuchen strukturiert an die Lösung zu kommen. Mir ist z.B. völlig schleierhaft wo deine Jahre (2011, 2012) herkommen. Danach wird nirgendwo abgefragt.
Auch solltest du dich festlegen (und den Unterschied kennen), ob du die Mengen per UND oder ODER verknüpfen möchtest. Und nicht zuletzt ist deine Gruppierung völliger Unsinn und nur MySQL sei Dank/Fluch überhaupt lauffähig. Deine Ergebnisse sind also erstmal sowieso schwammig...

Gruß Falk