Abfrage: Kund hat Artikel X aber nicht Y gekauft

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

[Delphi.Narium]

Da es mir keine Ruhe lässt, eine Lösung ohne Subselect zu finden, hab' ich mal den ChatBot meiner Wahl befragt, diese Lösung wurde extrem schnell geliefert:

Zitat von Frage:

Ich möchte alle Kunden haben, die den Artikel X gekauft haben und nie den Artikel Y gekauft haben. (Hinweis die Artikel X und Y können in verschiedenen Rechnungen des selben Kunden auftreten). Die Tabellen sehen so aus:

Kunden
------
KdNr, Name, ...

Rechnungen
-----------
KdNr, ReNr, ....

Positionen
----------
ReNr, Artikel, ....

Zitat von Antwort:

Sie können die folgende SQL-Abfrage verwenden, um alle Kunden zu finden, die Artikel X gekauft haben und nie Artikel Y gekauft haben. Bitte ersetzen Sie 'X' und 'Y' durch Ihre tatsächlichen Artikelnamen.

SQL-Code:

SELECT k.*
FROM Kunden k
WHERE EXISTS (
    SELECT 1
    FROM Rechnungen r
    JOIN Positionen p ON r.ReNr = p.ReNr
    WHERE r.KdNr = k.KdNr AND p.Artikel = 'X'
)
AND NOT EXISTS (
    SELECT 1
    FROM Rechnungen r
    JOIN Positionen p ON r.ReNr = p.ReNr
    WHERE r.KdNr = k.KdNr AND p.Artikel = 'Y'
)

Zitat von Fortsetzung der Antwort:

In dieser Abfrage wird zuerst geprüft, ob ein Kunde einen Artikel X gekauft hat ('EXISTS'-Teil), und dann wird sichergestellt, dass der Kunde nie einen Artikel Y gekauft hat ('NOT EXISTS'-Teil).

Aber, wir möchten es doch ohne Subselect, daher eine Nachfrage:

Zitat von Frage:

Gibt es auch eine Lösungsmöglichkeit für diese Fragestellung, die ohne Subselect auskommt?

Zitat von Antwort:

Ja, es ist möglich, eine Lösung ohne Subselects zu erstellen, indem Sie 'JOIN' und 'GROUP BY' verwenden. Hier ist ein Beispiel:

SQL-Code:

SELECT k.KdNr, k.Name
FROM Kunden k
JOIN Rechnungen r ON k.KdNr = r.KdNr
JOIN Positionen p ON r.ReNr = p.ReNr
GROUP BY k.KdNr, k.Name
HAVING SUM(CASE WHEN p.Artikel = 4701 THEN 1 ELSE 0 END) > 0
AND SUM(CASE WHEN p.Artikel = 5702 THEN 1 ELSE 0 END) = 0

Zitat von Fortsetzung der Antwort:

In dieser Abfrage wird die 'SUM'-Funktion mit einer 'CASE'-Anweisung verwendet, um zu zählen, wie oft jeder Kunde Artikel X und Y gekauft hat. Kunden, die Artikel X mindestens einmal gekauft haben und Artikel Y nie gekauft haben, werden in den Ergebnissen enthalten sein.

Bitte beachten Sie, dass diese Abfrage möglicherweise nicht so effizient ist wie die vorherige mit Subselects, insbesondere wenn Ihre Tabellen sehr groß sind. Es ist immer eine gute Idee, die Leistung zu überwachen und gegebenenfalls Indizes hinzuzufügen.

Es geht also doch

[jaenicke]

sum über case ist aber vermutlich die langsamste Variante, weil dies kaum optimiert werden kann. Meine letzte Variante ist auch kein echtes Subselect, weil die zweite Abfrage mit join kombiniert und nicht in jeder Zeile ausgeführt wird.

Holger hat mit seinem Einwand aber natürlich auch Recht:

Zitat von IBExpert:

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

[Uwe Raabe]

Zitat von Delphi.Narium:

Aber damit ist die Vermutung widerlegt:

Nicht zwingend.

SQL-Code:

SELECT K.KdNr, K.Name
FROM Kunden K
INNER JOIN Rechnungen R ON K.KdNr = R.KdNr
LEFT 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
GROUP BY K.KdNr, K.Name
HAVING COUNT(P1.ReNr) > 0 and COUNT(P2.ReNr) = 0;

Allerdings lassen die nur rudimentär vorhandenen Testdaten eigentlich keine belastbare Aussage zu.

Unschön ist halt, dass man die Kunden-Felder zumindest im GROUP BY einzeln aufführen muss (Ich kenne zumindest kein System, das dort ein K.* akzeptiert). Allerdings ist man dann vielleicht eher gewillt, nur die wirklich benötigten Felder anzugeben. Wäre ja denkbar, dass lediglich die KdNr benötigt wird, dann wird es recht simpel.

[Delphi.Narium]

Zitat von Uwe Raabe:

Zitat von Delphi.Narium:

Aber damit ist die Vermutung widerlegt:

Nicht zwingend.

Deshalb schrieb ich ja später auch

Zitat von Delphi.Narium:

Es geht also doch

Zitat von Uwe Raabe:

SQL-Code:

SELECT K.KdNr, K.Name
FROM Kunden K
INNER JOIN Rechnungen R ON K.KdNr = R.KdNr
LEFT 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
GROUP BY K.KdNr, K.Name
HAVING COUNT(P1.ReNr) > 0 and COUNT(P2.ReNr) = 0;

Das ist eine Abwandlung/Weiterentwicklung der von der KI weiter oben gelieferten Antwort oder hat zumindest große Ähnlichkeit mit ihr. Sie hat den Vorteil, dass sie ohne Case und Sum auskommt und sie ist dadurch deutlich eleganter und lesbarer (und vermutlich bei größeren Datenmengen auch schneller).

Zitat von Uwe Raabe:

Allerdings lassen die nur rudimentär vorhandenen Testdaten eigentlich keine belastbare Aussage zu.

Das war für mich auch das Hauptproblem erstmal Testdaten zu erstellen, die wirklich alle möglichen Fälle abdecken, hoffe aber, dass mir das in https://www.delphipraxis.net/1535576-post20.html gelungen ist.

Interessant ist: Wir haben jetzt acht unterschiedliche und funktionierende Lösungen zur Aufgabenstellung.