Abfrage: Kund hat Artikel X aber nicht Y gekauft

[BlueStarHH]

Hallo,

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:

Code:

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

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

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

Danke!

[Delphi.Narium]

Syntaktisch sehr altbackene Version:

SQL-Code:

select k1.*
from   kunden k1, rechnungen r1, positionen p1
where  k1.kdnr    = r1.kdnr
and    r1.renr    = p1.renr
and    p1.artikel = 'X'
and not exists
( 
  select 1
  from   kunden k2, rechnungen r2, positionen p2
  where  k2.kdnr    = r2.kdnr
  and    r2.renr    = p2.renr
  and    p2.artikel = 'Y'
  and    k1.kdnr    = k2.kdnr
)

[BlueStarHH]

Danke, das klappt!

[Delphi.Narium]

Noch ein Versuch:

SQL-Code:

select kdnr
from (
  select 
    kdnr, 
    Sum(HatX) as HatX, /* Wie oft kommt der Artikel X vor? */
    Sum(HatY) as HatY  /* Wie oft kommt der Artikel Y vor? */
  from (
    select 
      k1.kdnr, 
      case when p1.artikel = 'X' then 1 else 0 end as HatX, /* Jeder Artikel X zählt. */
      case when p1.artikel = 'Y' then 1 else 0 end as HatY  /* Jeder Artikel Y zählt. */
    from   kunden k1, rechnungen r1, positionen p1
    where  k1.kdnr = r1.kdnr
    and    r1.renr = p1.renr
    and    p1.artikel in ('X','Y') /* Alle Kunden, die irgendwo Artikel X oder Y erworben haben. */
  )
  group by kdnr
)
where HatY = 0; /* Nur die, bei denen Artikel Y nicht vorkommt. */

[Uwe Raabe]

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

So aus dem Ärmel, ohne das hier testen zu können:

SQL-Code:

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

[gubbe]

Zitat von Uwe Raabe:

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

So aus dem Ärmel, ohne das hier testen zu können:

SQL-Code:

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

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.

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

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