Select-Abfrage über mehrere Tabellen



  • Hallo,
    ich bräuchte Eure Hilfe für eine SELECT-Abfrage

    Gegeben sind die Tabellen

    Kunde          Artikel          Einkauf
    ------------   --------------   ------------
    |KdNr|KdName|  |ArtNr|ArtName|  |KdNr|ArtNr|
    |----|------|  |-----|-------|  |----|-----|
    | 1  |Meier |  |  1  |Apfel  |  | 1  |  2  |
    | 2  |Golz  |  |  2  |Käse   |  | 1  |  3  |
    | 3  |Huber |  |  3  |Salami |  | 1  |  4  |
    | 4  |Bauer |  |  4  |Seife  |  | 2  |  4  |
    | 5  |Beck  |  |  5  |Zeitung|  | 3  |  2  |
    -------------  |  6  |Eis    |  | 3  |  3  |
                   |  7  |Pfeffer|  | 4  |  5  |
                   ---------------  | 4  |  2  |
                                    | 5  |  1  |
                                    | 5  |  5  |
                                    ------------
    

    Aus diesen Tabellen muß ich per SQL-Anweisung die KdNamen selektieren, die sowohl Käse als auch Salami gekauft haben.

    Als Ergebnis müßte Meier und Huber und keinesfalls Bauer herauskommen.
    Mit meinem Versuch gelingt das leider nicht.

    SELECT DISTINCT k.KdName FROM db.Kunde k, db.Einkauf e
    WHERE
    (((k.KdNr = e.KdNr) AND (e.ArtNr = 2)) OR
     ((k.KdNr = e.KdNr) AND (e.ArtNr = 3)))
    

    Könnt Ihr mir weiterhelfen?



  • Schau dir mal an, wie Joins funktionieren. Gibt dazu IIRC ein paar recht brauchbare Wikipedia-Artikel und auch sonst viel Literatur.

    Und überleg dir, was genau du machen möchtest, dein Code selektiert nämlich einfach alle Kunden, die entweder Käse oder Salami gekauft haben.



  • Mich würde auch mal interessieren, wie man das ganze kompakt aufschreibt.

    Ich habe jetzt im Endeffekt für ein analoges Problem zwei fast identische Anfragen über ein INTERSECT miteinander verknüpft, aber das muss doch effizienter funktionieren? Ich wollte das ursprünglich mit EXISTS lösen, kam da aber überhaupt nicht weiter.



  • Weitere Möglichkeit (ungetestet):

    SELECT k.KdName
    FROM db.Kunde k
    WHERE EXISTS
    ( SELECT e.*
      FROM db.Einkauf e
      WHERE k.KdNr = e.KdNr
      AND (e.ArtNr = 2 OR e.ArtNr = 3))
    

    Oder

    SELECT k.KdName
    FROM db.Kunde k
    WHERE EXISTS
    ( SELECT e.*
      FROM db.Einkauf e
      WHERE k.KdNr = e.KdNr
      AND e.ArtNr = 2)
    UNION
    SELECT k.KdName
    FROM db.Kunde k
    WHERE EXISTS
    ( SELECT e.*
      FROM db.Einkauf e
      WHERE k.KdNr = e.KdNr
      AND e.ArtNr = 3)
    


  • Klappt leider beides nicht (mit PostgreSQL getestet), liefert alle Kunden zurück, die entwerder Produkt A und / oder Produkt B gekauft haben.

    Um ehrlich zu sein, verstehe ich auch nicht so ganz, wie EXISTS genau funktionieren soll. Ich hätte es nämlich genauso geschrieben und für mich macht das so auch Sinn.



  • Mit nur einem EXISTS kann es nicht gehen, da kommen klarerweise immer die Kunden zurück die einen ODER den anderen Artikel gekauft haben.
    Wenn dann braucht man zwei EXISTS

    SELECT k.KdName
    FROM Kunde k
    WHERE EXISTS (SELECT * FROM Einkauf AS e1 WHERE e1.KdNr = k.KdNr AND e1.ArtNr = 2)
      AND EXISTS (SELECT * FROM Einkauf AS e2 WHERE e2.KdNr = k.KdNr AND e2.ArtNr = 3)
    

    (On die Unterschiedlichen Table-Alias (e1, e2) nötig sind, weiss ich nicht. Sollte vermutlich auch mit 2x bloss "e" funktionieren, nur da ich mir nicht sicher bin...)

    Alternativ könnte man mit GROUP BY und HAVING COUNT(*) = 2 arbeiten. Würde ich aber nur machen, wenn die Artikelzahl auch mal gross werden kann (mehr als nur 2-3 verschiedene Artikel).

    Beispiel:

    SELECT k.KdNr, k.KdName
    FROM Kunde AS k
        INNER JOIN
            (SELECT DISTINCT KdNr, ArtNr FROM Einkauf WHERE ArtNr IN (2, 3)) AS e
            ON e.KdNr = k.KdNr
    GROUP BY k.KdNr, k.KdName
    HAVING COUNT(*) = 2
    

    Oder so ähnlich.
    (Wenn das DISTINCT bereits so garantiert ist, dann kann das hässliche Sub-Select natürlich weg)

    EDIT: 2. SQL Schnippsel korrigiert.

    Nochmal EDIT: es geht natürlich auch mit 2x LEFT JOIN und IS NOT NULL, sollte klar sein. (EXISTS kann man ja oft mit LEFT JOIN und IS NOT NULL ersetzen. Ich finde die EXISTS Schreibweise aber besser verständlich, daher spar ich mir auch das LEFT JOIN Beispiel)



  • Nur zwei simple joins auf die Kundennummer reichen doch:

    SELECT k.KdName FROM Einkauf e1, Einkauf e2, Kunden k
    -- join auf KdNr
    WHERE e1.KdNr = k.KdNr
    AND e2.KdNr = k.KdNr
    AND e1.ArtNr = 2 
    AND e2.ArtNr = 3
    

    Sollte performanter sein als subselects, having, exists und der ganze Kram.



  • Sollte performanter sein als subselects, having, exists und der ganze Kram.

    Kommt auf den Server an. Mit MSSQL ist das hübsch egal. Also zumindest JOIN vs. EXISTS nimmt sich da nix.



  • hustbaer schrieb:

    Sollte performanter sein als subselects, having, exists und der ganze Kram.

    Kommt auf den Server an.

    Kommt auch auf Indizes an und wie der Server damit und mit eventuell vorhandenen Statistiken zurandekommt. Auf meiner DB2 hat ein Index auf KdNr in beiden Tabellen gereicht, um das Ganze verdammt fix zu machen. Das Exists hat den index dagegen rausgehauen, da war ein full table scan nötig...



  • Versteh jetzt nicht was du meinst.
    Ohne Index ist immer lahm.

    Und mit passenden Indexen kommts drauf an wie schlau der Query-Optimizer des verwendeten Servers ist. Genau das meinte ich mit "kommt auf den Server an".



  • Das sollte auch funktionieren:

    SELECT KdName
    FROM Kunde INNER JOIN
    (
      SELECT KdNr ,GROUP_CONCAT(ArtNr) AS Artikel 
      FROM Einkauf GROUP BY KdNr
    ) AS E
    ON Kunde.KdNr = E.KdNr
    WHERE Find_In_Set(2,Artikel) AND Find_In_Set(3,Artikel)
    


  • hustbaer schrieb:

    Versteh jetzt nicht was du meinst.
    Ohne Index ist immer lahm.

    Ja, aber es gibt Dinge, die den index nutzlos machen, weil die DB dann trotz Index einen Full Table Scan machen muss. Exists gehört dazu.



  • pumuckl schrieb:

    hustbaer schrieb:

    Versteh jetzt nicht was du meinst.
    Ohne Index ist immer lahm.

    Ja, aber es gibt Dinge, die den index nutzlos machen, weil die DB dann trotz Index einen Full Table Scan machen muss. Exists gehört dazu.

    Öh. Nö 🙂 Das stimmt nicht.
    MS SQL Server kann EXISTS wunderbar optimieren, kein unnötiger Table-Scan.

    // 1
    SELECT * FROM a WHERE EXISTS (SELECT * FROM b WHERE b.nr = b.nr)
    
    // 2
    SELECT * FROM a WHERE nr IN (SELECT nr FROM b)
    
    // 3
    SELECT a.* FROM a
        INNER JOIN b ON b.nr = a.nr
    

    Ergeben alle den *exakt* gleichen Execution-Plan.

    EDIT: Fehler korrigiert. Bei (3) muss es SELECT a.* heissen damit genau der selbe Execution-Plan rauskommt, nicht einfach SELECT *. Ist auch logisch. /EDIT



  • pumuckl schrieb:

    Ja, aber es gibt Dinge, die den index nutzlos machen, weil die DB dann trotz Index einen Full Table Scan machen muss. Exists gehört dazu.

    Fakt ist, das solche Aussagen immer auf spezifische Datenbanken beschränkt sind. Wir hatten einige Fälle (Oracle) wo EXISTS sogar schneller als die Alternativen waren. Das hängt natürlich auch an mehreren Faktoren.


Log in to reply