Abfrage: Kund hat Artikel X aber nicht Y gekauft

[BlueStarHH]

Zitat von Uwe Raabe:

Zitat von gubbe:

Guter Ansatz, aber da würde mindestens noch ein "group by" fehlen, sonst lässt sich nichts zählen und "having" allein ergibt keinen Sinn.

In der Tat, die Zeile ist mir beim Übertragen wohl verloren gegangen.

Aber selbst damit klappt es merkwürdiger weise nicht. Ergebnis ist komplett leer, obwohl das SQL OK aussieht.
Druch das "group by" ist auch kein "select k1.*" mehr möglich.

[Uwe Raabe]

Ich würde das ja auch lieber an realen Daten ausprobieren. In der Theorie sieht ja vieles einfacher aus als es hinterher in der Praxis ist.

[Delphi.Narium]

Wenn bei dem SQL ein Kunde keinen Artikel Y gekauft hat, ist die Ergebnismenge leer, egal wieviele X er gekauft hat.

Wenn ein Kunde zwar Artikel Y gekauft hat, aber keinen Artikel X ist die Ergebnismenge ebenfalls leer. Gut, die Kunden interessieren uns nicht.

Nur wenn ein Kunde X und Y gekauft hat und diese sich in der gleichen Rechnung befinden, wird die Ergebnismenge nicht leer sein. Aber sie enthält dann nur Kunden, die wir nicht haben wollen.

Positionen p2 muss über eine eigene Rechnung mit den Kunden verbunden werden, um sicherzustellen, dass Artikel X und Artikel Y nicht in einer Rechnung erscheinen müssen.

SQL-Code:

select k1.kdnr
from kunden k1
join rechnungen r1 on r1.kdnr = k1.kdnr
join positionen p1 on p1.renr = r1.renr and p1.artikel = 'X'
join rechnungen r2 on r2.kdnr = k1.kdnr
join positionen p2 on p2.renr = r2.renr and p2.artikel = 'Y'
having (Count(p1.artiklel) > 0) and (Count(p2.artikel) = 0)

Auch das wird noch nicht funktionieren, Frage ist, wo genau muss hier ein Left vor den Join und wie kann man dann sicherstellen, dass wirklich nur die Kunden erscheinen, die Artikel X erworben haben? Momentan hab' ich dafür absolut keine Idee. Bin mir nicht sicher, ob ein Left vor Positionen p2 ausreicht oder doch irgendwelche unerwünschten Nebenwirkungen hat. Ohne Daten und entsprechende Tabellen ist das halt doch eher Gehirnjogging mit großem Ratefaktor, als fundierte Lösungsfindung.

Aggregatfunktionen benötigen immer ein Group by. Select * funktioniert bei Group by nie. Alles, was im Select aufgeführt wird, muss entweder im Group By stehen oder als Aggregatfunktion im Select erscheinen.

[jaenicke]

Wie wäre es so?

SQL-Code:

SELECT DISTINCT K.*
FROM Kunden K
INNER JOIN Rechnungen R ON K.KdNr = R.KdNr
INNER JOIN Positionen P1 ON R.ReNr = P1.ReNr AND P1.Artikel = A
LEFT JOIN Positionen P2 ON R.ReNr = P2.ReNr AND P2.Artikel = B
WHERE P2.ReNr IS NULL;

Eine Zählung ist ja nicht notwendig. Es geht ja nur um die Existenz.

Testcode für https://sqliteonline.com/

SQL-Code:

create table Kunden (
    KdNr int,
    Name varchar(255)
);

create table Rechnungen (
    KdNr int,
    ReNr int
);

create table Positionen (
    ReNr int,
    Artikel int
);

insert into Kunden (KdNr, Name) VALUES (1, 'Meier');
insert into Kunden (KdNr, Name) VALUES (2, 'Schulz');

insert into Rechnungen (KdNr, ReNr) VALUES (1, 100);
insert into Rechnungen (KdNr, ReNr) VALUES (2, 200);

insert into Positionen (ReNr, Artikel) VALUES (100, 4701);
insert into Positionen (ReNr, Artikel) VALUES (100, 4702);
insert into Positionen (ReNr, Artikel) VALUES (200, 4701);
insert into Positionen (ReNr, Artikel) VALUES (200, 5702);


SELECT DISTINCT K.*
FROM Kunden K
INNER JOIN Rechnungen R ON K.KdNr = R.KdNr
INNER JOIN Positionen P1 ON R.ReNr = P1.ReNr AND P1.Artikel = 4701
LEFT JOIN Positionen P2 ON R.ReNr = P2.ReNr AND P2.Artikel = 5702
WHERE P2.ReNr IS NULL;

[Uwe Raabe]

Zitat von jaenicke:

SQL-Code:

SELECT DISTINCT K.*
FROM Kunden K
INNER JOIN Rechnungen R ON K.KdNr = R.KdNr
INNER JOIN Positionen P1 ON R.ReNr = P1.ReNr AND P1.Artikel = A
LEFT JOIN Positionen P2 ON R.ReNr = P2.ReNr AND P2.Artikel = B
WHERE P2.ReNr IS NULL;

So nur vom drüberlesen: Fallen da nicht alle Rechnungen raus, bei denen kein A aber ein B enthalten sind?

[jaenicke]

Zitat von Uwe Raabe:

So nur vom drüberlesen: Fallen da nicht alle Rechnungen raus, bei denen kein A aber ein B enthalten sind?

Das ist ja auch die Anforderung. Es sollen alle Kunden, die Artikel A gekauft haben, drin sein, und von denen nur die, die B nicht gekauft haben.

Man könnte auch nur LEFT JOINs verwenden, aber sofern die Tabellen indiziert sind, sollte es mit dem INNER JOIN schneller sein. Beim dritten JOIN muss wiederum ein LEFT JOIN verwendet werden, damit die Nullwerte erhalten bleiben.

[Uwe Raabe]

Zitat von jaenicke:

Das ist ja auch die Anforderung. Es sollen alle Kunden, die Artikel A gekauft haben, drin sein, und von denen nur die, die B nicht gekauft haben.

Das ist mir schon klar, aber mit

Code:

INNER JOIN Positionen P1 ON R.ReNr = P1.ReNr AND P1.Artikel = A

werden ja nur Rechnungen selektiert, bei denen mindestens ein A in den Positionen vorkommt. Damit werden Rechnungen, bei denen kein A aber ein B vorkommt nicht berücksichtigt. Wenn also ein Kunde eine Rechnung nur mit A und eine nur mit B hat, würde er fälschlicherweise in die Ergebnismenge aufgenommen. Oder sehe ich das falsch?

[Jasocul]

Zitat von BlueStarHH:

Zitat von Uwe Raabe:

Zitat von gubbe:

Guter Ansatz, aber da würde mindestens noch ein "group by" fehlen, sonst lässt sich nichts zählen und "having" allein ergibt keinen Sinn.

In der Tat, die Zeile ist mir beim Übertragen wohl verloren gegangen.

Aber selbst damit klappt es merkwürdiger weise nicht. Ergebnis ist komplett leer, obwohl das SQL OK aussieht.
Druch das "group by" ist auch kein "select k1.*" mehr möglich.

Natürlich ist das Ergebnis leer. Da sollte ein "outer join" helfen.

Code:

select k2.*
from (
select k1.kdnr
from kunden k1
join rechnungen r1 on r1.kdnr = k1.kdnr
join positionen p1 on p1.renr = r1.renr and p1.artikel = 'X'
left outer join positionen p2 on p2.renr = r1.renr and p2.artikel = 'Y'
group by k1.kdnr
having (Count(p1.artiklel) > 0) and (Count(p2.artikel) = 0)) as Treffer
join kunden k2 on k2.kdnr = Treffer.kdnr

oder

Code:

select k2.*
from kunden k2
where k2.kdnr in (
select k1.kdnr
from kunden k1
join rechnungen r1 on r1.kdnr = k1.kdnr
join positionen p1 on p1.renr = r1.renr and p1.artikel = 'X'
left outer join positionen p2 on p2.renr = r1.renr and p2.artikel = 'Y'
group by k1.kdnr
having (Count(p1.artiklel) > 0) and (Count(p2.artikel) = 0))

Es gibt meines Wissens aber DBs, die eine Begrenzung bei der Anzahl im Ergebnis von "in" haben. Vermutlich ist die zweite Variante bei großen Datenmengen auch langsamer.
So müssten auch alle Kundenfelder verfügbar sein.
Es gibt auch noch andere Lösungen. Man könnte z.B. mit "with" arbeiten oder auf der DB passende Views erstellen.

Die SQL sind ungetestet. Könnte also sein, dass das nicht auf Anhieb funktioniert.

[IBExpert]

ich finde sowas einfacher zu verstehen und modularer, weil auch mit anderen bedingungen einfach zu kombinieren und trotzdem
die unterbedingungen einfach lesbar sind.

Code:

SELECT DISTINCT K.*
FROM Kunden K
where
exists(select * from Rechnungen R JOIN Positionen P1 ON R.ReNr = P1.ReNr AND P1.Artikel = 4701 where K.KdNr = R.KdNr)
and 
not exists(select * from Rechnungen R JOIN Positionen P1 ON R.ReNr = P1.ReNr AND P1.Artikel = 5702 where K.KdNr = R.KdNr)