Verknüpfungstabelle AND Abfrage

[theo]

Kann mir mal jemand auf die Sprünge helfen, mit einem MySQL Problemchen?

Ich habe drei Tabellen.

Eine Tabelle "werke" (Musikstücke, z.B. Feld "title")
eine Tabelle "stile" (z.B Feld "stilname_id" also jazz, classic etc.)
und eine Verknüpfungstabelle (werke_stile)

Nun möchte ich jene Musikstücke abfragen, die sowohl den "classic" als auch den "jazz" Stil zugewiesen haben.
So bekomme ich bswp. alle titel, die "classic" ODER "jazz" zugewiesen haben:

Code: Alles auswählen

SELECT DISTINCT titel FROM werke AS we INNER JOIN werke_stile AS ws ON ws.werk_id=we.werk_id INNER JOIN stile AS st ON ws.stil_id = st.id WHERE st.stilname_id='jazz' OR st.stilname_id='classic'

Wie bekomme ich alle titel, welche "classic" UND "jazz" zugewiesen haben?

Danke!

[sstvmaster]

Aber DISTINCT entfernt doch alle doppelten Einträge?

Code: Alles auswählen

SELECT titel FROM werke AS we INNER JOIN werke_stile AS ws ON ws.werk_id=we.werk_id INNER JOIN stile AS st ON ws.stil_id = st.id WHERE st.stilname_id='jazz' AND st.stilname_id='classic'

Ist jetzt nur geraten.

[Warf]

Code: Alles auswählen

SELECT DISTINCT titel FROM werke AS we INNER JOIN werke_stile AS ws ON ws.werk_id=we.werk_id INNER JOIN stile AS st ON ws.stil_id = st.id INNER JOIN werke_stile AS ws2 ON ws2.werk_id=we.werk_id INNER JOIN stile AS st2 ON ws2.stil_id = st2.id  WHERE st.stilname_id='jazz' AND st2.stilname_id='classic'

Doppelter join, so würd ichs machen, obs noch anders geht, keine ahnung

WHERE st.stilname_id='jazz' AND st.stilname_id='classic'

Da wird der query optimizer sagen: Das ist trival falsch (entweder es st.stilname_id ist 'jazz' oder st.stilname_id ist 'classic', aber ein feld kann nicht gleichzeitig beides sein) und würde nix zurückgeben (das aber sehr effizient)

[theo]

Aber DISTINCT entfernt doch alle doppelten Einträge?

Danke für die Antwort.
Nein, so einfach ist es nicht.
Es gibt so ja nicht EINEN Datensatz, wo beides zutrifft. DISTINCT hin oder her.
Also wie frage ich das ab?

[theo]

Code: Alles auswählen

SELECT DISTINCT titel FROM werke AS we INNER JOIN werke_stile AS ws ON ws.werk_id=we.werk_id INNER JOIN stile AS st ON ws.stil_id = st.id INNER JOIN werke_stile AS ws2 ON ws2.werk_id=we.werk_id INNER JOIN stile AS st2 ON ws2.stil_id = st2.id  WHERE st.stilname_id='jazz' AND st2.stilname_id='classic'

Doppelter join, so würd ichs machen, obs noch anders geht, keine ahnung

Danke, so scheint es zu gehen. Da wäre ich nicht drauf gekommen.

WHERE st.stilname_id='jazz' AND st.stilname_id='classic'

Da wird der query optimizer sagen: Das ist trival falsch (entweder es st.stilname_id ist 'jazz' oder st.stilname_id ist 'classic', aber ein feld kann nicht gleichzeitig beides sein) und würde nix zurückgeben (das aber sehr effizient)

Ja, das ist mir klar.

Danke!

[af0815]

Ich würde einen Sub-Select machen auf die Stile, dort die gewünschten Stile filtern und anschliessend erst den Join machen.

having nach dem join würde sich auch anbieten.

[theo]

Ich würde einen Sub-Select machen auf die Stile, dort die gewünschten Stile filtern und anschliessend erst den Join machen.

Das hatte ich auch als "Notlösung" im Kopf, hatte aber noch auf einen eleganteren Weg gehofft, wie den von Warf.

Siehst du einen Vorteil im "Sub-Select"?

Kannst du ein Beispiel machen, wie das im vorliegenden Fall konkret aussehen würde? (Die Syntax ist doch oft recht "picky")

[af0815]

Ist das noch aktuell ? Ich habe zwar nur MS-SQL zur Verfügung, die Unterschiede bei den Standbefehlen sollten es aber nicht ausmachen.

[theo]

Ist das noch aktuell ? Ich habe zwar nur MS-SQL zur Verfügung, die Unterschiede bei den Standbefehlen sollten es aber nicht ausmachen.

Ich denke so ein Beispiel ist doch immer aktuell, falls es dir keine zu große Mühe macht.
V.A. habe ich festgestellt, dass die Suche nach solchen Problemen mit Google recht schwierig ist, wenn man nicht genau weiß, welche Begriffe (bzw. Lösungen) sich anbieten.
Die reine Problembeschreibung (wie oben) mit Google führt oft nicht zum Ziel.

Mich würde auch interessieren, wo du den Vorteil siehst, gegenüber der Lösung mit dem doppelten Join.

[af0815]

Hier mal das Script zur Erstellung der Tabellen und Daten (MS-SQL Dialekt)

[af0815]

Code: Alles auswählen

SELECT         st.stilname, dbo.werke.titel
FROM            (SELECT        stile_id, stilname
                 FROM            dbo.stile
                 WHERE (dbo.stile.stilname = 'jazz') OR (dbo.stile.stilname = 'classic')) AS st 
  INNER JOIN  dbo.werke_stile ON st.stile_id = dbo.werke_stile.stil_id 
  INNER JOIN dbo.werke ON dbo.werke_stile.werk_id = dbo.werke.werk_id

Ergebnis

Code: Alles auswählen

 
stilname	titel
classic	classic1
classic	classic2
jazz	jazz1
jazz	jazz2
jazz	jazz3
classic	classic3
classic	funk1
jazz	funk1
jazz	gemischt1
classic	gemischt2
jazz	gemischt2
jazz	gemischt3

Code: Alles auswählen

SELECT        dbo.stile.stilname, dbo.werke.titel
FROM            dbo.werke_stile INNER JOIN
                         dbo.werke ON dbo.werke_stile.werk_id = dbo.werke.werk_id INNER JOIN
                         dbo.stile ON dbo.werke_stile.stil_id = dbo.stile.stile_id
WHERE        (dbo.stile.stilname = 'jazz') OR
                         (dbo.stile.stilname = 'classic')

Ergebnis

Code: Alles auswählen

stilname	titel
classic	classic1
classic	classic2
jazz	jazz1
jazz	jazz2
jazz	jazz3
classic	classic3
classic	funk1
jazz	funk1
jazz	gemischt1
classic	gemischt2
jazz	gemischt2
jazz	gemischt3

[af0815]

für das reine Ergebnis ist es egal. Wenn du mit Datenmengen im Join arbeitest so kann es von Vorteil sein, die Datenmengen mal zu beschneiden, daher mit dem inneren Select die Datenmenge der Oder Verknüpfung klein zu halten und die Joins dannach auf ein Minimum zu reduzieren.

Manche Vorgangsweisen prägst du die im laufe der Jahre ein, besonders wenn du laufend mit echten Mengen arbeitest. Beim MS-SQL Server Verwaltungstool kannst du dir die Executio-Plans ansehen und auch auswerten (mit Erfahrung). Da ich am MS-SQL einiges an Ausbildung genossen habe, sehe ich manche Dinge halt komplizierter

Das geht natürlich auch (Ich habe gerade gesehen das das 'IN' Statement auch bei MySQL geht. Das ist genaugenommen ein implizites SubQuery. Allerdings als Parameter trau ich mir die Lösungen nicht definieren

Code: Alles auswählen

SELECT       dbo.stile.stilname, dbo.werke.titel
FROM            dbo.werke_stile INNER JOIN
                         dbo.werke ON dbo.werke_stile.werk_id = dbo.werke.werk_id INNER JOIN
                         dbo.stile ON dbo.werke_stile.stil_id = dbo.stile.stile_id
WHERE        dbo.stile.stilname IN ('jazz','classic')

Viele (komplizierte) Wege führen nach Rom.

Edit: Beim, IN kann man die als Parameter verwenden - nur einzeln (wie beim OR - Statement).

[theo]

@af0815: Danke! Das schaue ich mir morgen noch genauer an.

[af0815]

Übrigends kann man den kleinen, für private Zwecke freien MS-SQL auch auf Ubuntu installieren. Geht mit der Beschreibung von MS sehr gut. Den habe ich in einer VM bei mir, deswegen mache ich das Testen auf dem. Die Verwaltungs und Programmiertools von MS laufen dafür nur unter Windows. Was vergleichbares habe ich nicht unter Linux gefunden.

Die Feinheiten und Unterschiede bei der SQL-Abfrage kommen erst zum Tragen, wenn man Datenmengen hat.Nicht nur ein paar Einträge. Da sollte man schon auch Indizes planen, auf die die Logik beim Abfragen zugreifen kann. Als Faustregel kann man sagen, jeder physikalische Zugriff auf die Platte ist extrem teuer. Die Daten aus den Indextabellen liegen meist im Cache und sind deswegen wesentlich billiger. Deswegen sollte man auch Trachten die Mengen soweit wie möglich Einzugrenzen. Die Optimierer von MS sind mittlerweile extrem gut und stellen die Queries intern wirklich optimal um. Ich muss mir dann oft wirklich ansehen, warum Indexe nicht verwendet wurden, obwohl ich geglaubt habe, die sind gut.

Wenn es möglich ist, lasse ich die Abfragen wie Views am Server mit Parametern. Das hat IMHO zwei Vorteile. Der Server kann den kompilierten Executionplan selbst cachen und den weiteren cache optimieren. Parameter werden vom Server auch geprüft und können kein weiteres ausführbares SQL enthalten. Ein Parameter wird eingesetzt aber nicht kompiliert. Damit kann man unabsichtliche SQL Injections schon mal unterbinden.
Das ist auch der Grund, warum ich bei den IN nicht die ganze Engrenzung als Parameter machen kann, sondern nur einzeln.. Daherkann ich classic und jazz nur getrennt als zwei Parameter übergeben und nicht als einen, mit Hochkommas und Beistrichen. Denn das wäre bereits SQL und kein Parameter. Der Server macht die Abfrage auch ohne Probleme, nur du bekommst kein Resultset, weil die IN Klausel nicht Erfüllt ist. Muss man wissen, weil man sonst sich fragt, warum das so nicht funktioniert. Allerdings kommen aus diesen Nichtwissen dann die Stellen in der Software wo SQL Injections gemacht werden können, weil halt die Statements zusammengefrickelt werden, anstatt. vernünftig mit Parametern zu arbeiten. Wobei die Statements schon mit dem Programm gemacht werden können, die Daten für die Abfrage aber über Parameter laufen sollen.

[theo]

@af0815: Danke nochmals.

Vielleicht stehe ich jetzt komplett auf dem Schlauch, aber bei deinen Beispielen kommt für mich nicht das gewünschte heraus.
Ich möchte nur Resultate, welche beide Stile haben (UND Verknüpfung, nicht OR)
Das erreiche ich mit:

Code: Alles auswählen

SELECT DISTINCT titel FROM werke AS we 
INNER JOIN werke_stile AS ws ON ws.werk_id=we.werk_id 
INNER JOIN stile AS st ON ws.stil_id = st.stil_id 
INNER JOIN werke_stile AS ws2 ON ws2.werk_id=we.werk_id 
INNER JOIN stile AS st2 ON ws2.stil_id = st2.stil_id 
WHERE st.stilname_id='jazz' AND st2.stilname_id='classic' 
 

Danke!