Abfrage: Kund hat Artikel X aber nicht Y gekauft

[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;

[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)

[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?

[jaenicke]

Nein, denn wie du sagst, die Kunden mit B fliegen in der Zeile ja schon raus.

[Uwe Raabe]

Hmmm, ich habe mal dein Beispiel entsprechend ergänzt:

Zitat von SQL:

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 Rechnungen (KdNr, ReNr) VALUES (1, 300);

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);
insert into Positionen (ReNr, Artikel) VALUES (300, 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;

Kunde 1 (Meier) hat nun Rechnung 100 mit 4701 und Rechnung 300 mit 5702, hat also A und B gekauft. Trotzdem wird er im Ergebnis angezeigt.

[jaenicke]

Ja, da hast du Recht. Mit einem join in der Form sollte es aber gehen:

SQL-Code:

SELECT DISTINCT K.KdNr, K.Name
FROM Kunden K
INNER JOIN Rechnungen R ON K.KdNr = R.KdNr
INNER JOIN Positionen PA ON R.ReNr = PA.ReNr AND PA.Artikel = 4701
LEFT JOIN (
    SELECT DISTINCT R.KdNr
    FROM Rechnungen R
    INNER JOIN Positionen PB ON R.ReNr = PB.ReNr
    WHERE PB.Artikel = 5702
) AS KundenMitArtikelB ON K.KdNr = KundenMitArtikelB.KdNr
WHERE KundenMitArtikelB.KdNr IS NULL;

[Delphi.Narium]

Aber damit ist die Vermutung widerlegt:

Zitat von Uwe Raabe:

Das müsste doch aber auch mit Joins anstatt Sub-Selects funktionieren.

'ne weitere Alternative, auch mit 'nem Subselect:

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 Rechnungen (KdNr, ReNr)    VALUES (  1, 100);
insert into Rechnungen (KdNr, ReNr)    VALUES (  1, 101);
insert into Positionen (ReNr, Artikel) VALUES (100, 4701);
insert into Positionen (ReNr, Artikel) VALUES (100, 4702);
insert into Positionen (ReNr, Artikel) VALUES (101, 5702);

insert into Kunden     (KdNr, Name)    VALUES (  2, 'Schulz');
insert into Rechnungen (KdNr, ReNr)    VALUES (  2, 200);
insert into Positionen (ReNr, Artikel) VALUES (200, 4701);
insert into Positionen (ReNr, Artikel) VALUES (200, 5702);

insert into Kunden     (KdNr, Name)    VALUES (  3, 'Gärtner');
insert into Rechnungen (KdNr, ReNr)    VALUES (  3, 300);
insert into Positionen (ReNr, Artikel) VALUES (300, 4701);

insert into Kunden     (KdNr, Name)    VALUES (  4, 'Bauer');
insert into Rechnungen (KdNr, ReNr)    VALUES (  4, 400);
insert into Positionen (ReNr, Artikel) VALUES (400, 4702);

commit;

select kdnr, name 
from (
  select K.kdnr, k.name,
  sum(case when Coalesce(p1.ReNr,0) <> 0 then 1 else 0 end) as HatX, 
  sum(case when Coalesce(p2.ReNr,0) <> 0 then 1 else 0 end) as HatY
  from Kunden K
  inner join Rechnungen R1 on K.KdNr  = R1.KdNr
  left  join Positionen P1 on R1.ReNr = P1.ReNr and P1.Artikel = 4701
  inner join Rechnungen R2 on K.KdNr  = R2.KdNr
  left  join Positionen P2 on R2.ReNr = P2.ReNr and P2.Artikel = 5702
  group by
    k.kdnr, k.name
)
where HatX > 0 and HatY = 0
order by
  kdnr, name

Schade, dass FireBird Minus nicht unterstützt, dann wäre ein einfaches

SQL-Code:

select * from kunden
join rechnungen on rechnungen.kdnr = kunden.kdnr
join positionen on positionen.renr = rechnungen.renr and positionen.artikel = 4701
minus
select * from kunden
join rechnungen on rechnungen.kdnr = kunden.kdnr
join positionen on positionen.renr = rechnungen.renr and positionen.artikel = 5702

möglich.