Verknüpfungstabelle AND Abfrage

Für Themen zu Datenbanken und Zugriff auf diese. Auch für Datenbankkomponenten.
Benutzeravatar
theo
Beiträge: 10468
Registriert: Mo 11. Sep 2006, 19:01

Verknüpfungstabelle AND Abfrage

Beitrag von 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
Beiträge: 575
Registriert: Sa 22. Okt 2016, 23:12
OS, Lazarus, FPC: W10, L 2.2.6
CPU-Target: 32+64bit
Wohnort: Dresden

Re: Verknüpfungstabelle AND Abfrage

Beitrag von 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.
LG Maik

Windows 10,
- Lazarus 2.2.6 (stable) + fpc 3.2.2 (stable)
- Lazarus 2.2.7 (fixes) + fpc 3.3.1 (main/trunk)

Warf
Beiträge: 1908
Registriert: Di 23. Sep 2014, 17:46
OS, Lazarus, FPC: Win10 | Linux
CPU-Target: x86_64

Re: Verknüpfungstabelle AND Abfrage

Beitrag von 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)

Benutzeravatar
theo
Beiträge: 10468
Registriert: Mo 11. Sep 2006, 19:01

Re: Verknüpfungstabelle AND Abfrage

Beitrag von theo »

sstvmaster hat geschrieben: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?

Benutzeravatar
theo
Beiträge: 10468
Registriert: Mo 11. Sep 2006, 19:01

Re: Verknüpfungstabelle AND Abfrage

Beitrag von theo »

Warf hat geschrieben:

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. :roll:

Warf hat geschrieben:
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!

Benutzeravatar
af0815
Lazarusforum e. V.
Beiträge: 6200
Registriert: So 7. Jan 2007, 10:20
OS, Lazarus, FPC: FPC fixes Lazarus fixes per fpcupdeluxe (win,linux,raspi)
CPU-Target: 32Bit (64Bit)
Wohnort: Burgenland
Kontaktdaten:

Re: Verknüpfungstabelle AND Abfrage

Beitrag von 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.
Blöd kann man ruhig sein, nur zu Helfen muss man sich wissen (oder nachsehen in LazInfos/LazSnippets).

Benutzeravatar
theo
Beiträge: 10468
Registriert: Mo 11. Sep 2006, 19:01

Re: Verknüpfungstabelle AND Abfrage

Beitrag von theo »

af0815 hat geschrieben: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")

Benutzeravatar
af0815
Lazarusforum e. V.
Beiträge: 6200
Registriert: So 7. Jan 2007, 10:20
OS, Lazarus, FPC: FPC fixes Lazarus fixes per fpcupdeluxe (win,linux,raspi)
CPU-Target: 32Bit (64Bit)
Wohnort: Burgenland
Kontaktdaten:

Re: Verknüpfungstabelle AND Abfrage

Beitrag von af0815 »

Ist das noch aktuell ? Ich habe zwar nur MS-SQL zur Verfügung, die Unterschiede bei den Standbefehlen sollten es aber nicht ausmachen.
Zuletzt geändert von af0815 am Sa 26. Okt 2019, 09:33, insgesamt 1-mal geändert.
Blöd kann man ruhig sein, nur zu Helfen muss man sich wissen (oder nachsehen in LazInfos/LazSnippets).

Benutzeravatar
theo
Beiträge: 10468
Registriert: Mo 11. Sep 2006, 19:01

Re: Verknüpfungstabelle AND Abfrage

Beitrag von theo »

af0815 hat geschrieben: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.

Benutzeravatar
af0815
Lazarusforum e. V.
Beiträge: 6200
Registriert: So 7. Jan 2007, 10:20
OS, Lazarus, FPC: FPC fixes Lazarus fixes per fpcupdeluxe (win,linux,raspi)
CPU-Target: 32Bit (64Bit)
Wohnort: Burgenland
Kontaktdaten:

Re: Verknüpfungstabelle AND Abfrage

Beitrag von af0815 »

Hier mal das Script zur Erstellung der Tabellen und Daten (MS-SQL Dialekt)
Dateianhänge
theoscript.sql.txt
Script für Tabellen und Daten
(7.58 KiB) 156-mal heruntergeladen
Blöd kann man ruhig sein, nur zu Helfen muss man sich wissen (oder nachsehen in LazInfos/LazSnippets).

Benutzeravatar
af0815
Lazarusforum e. V.
Beiträge: 6200
Registriert: So 7. Jan 2007, 10:20
OS, Lazarus, FPC: FPC fixes Lazarus fixes per fpcupdeluxe (win,linux,raspi)
CPU-Target: 32Bit (64Bit)
Wohnort: Burgenland
Kontaktdaten:

Re: Verknüpfungstabelle AND Abfrage

Beitrag von 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
Blöd kann man ruhig sein, nur zu Helfen muss man sich wissen (oder nachsehen in LazInfos/LazSnippets).

Benutzeravatar
af0815
Lazarusforum e. V.
Beiträge: 6200
Registriert: So 7. Jan 2007, 10:20
OS, Lazarus, FPC: FPC fixes Lazarus fixes per fpcupdeluxe (win,linux,raspi)
CPU-Target: 32Bit (64Bit)
Wohnort: Burgenland
Kontaktdaten:

Re: Verknüpfungstabelle AND Abfrage

Beitrag von 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 :-)

Anmerkung 2019-10-26 144856.png


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. :mrgreen:

Edit: Beim, IN kann man die als Parameter verwenden - nur einzeln (wie beim OR - Statement).
Blöd kann man ruhig sein, nur zu Helfen muss man sich wissen (oder nachsehen in LazInfos/LazSnippets).

Benutzeravatar
theo
Beiträge: 10468
Registriert: Mo 11. Sep 2006, 19:01

Re: Verknüpfungstabelle AND Abfrage

Beitrag von theo »

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

Benutzeravatar
af0815
Lazarusforum e. V.
Beiträge: 6200
Registriert: So 7. Jan 2007, 10:20
OS, Lazarus, FPC: FPC fixes Lazarus fixes per fpcupdeluxe (win,linux,raspi)
CPU-Target: 32Bit (64Bit)
Wohnort: Burgenland
Kontaktdaten:

Re: Verknüpfungstabelle AND Abfrage

Beitrag von 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.
Blöd kann man ruhig sein, nur zu Helfen muss man sich wissen (oder nachsehen in LazInfos/LazSnippets).

Benutzeravatar
theo
Beiträge: 10468
Registriert: Mo 11. Sep 2006, 19:01

Re: Verknüpfungstabelle AND Abfrage

Beitrag von 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!

Antworten