Verknüpfungstabelle AND Abfrage

Für Themen zu Datenbanken und Zugriff auf diese. Auch für Datenbankkomponenten.

Verknüpfungstabelle AND Abfrage

Beitragvon theo » 15. Okt 2019, 13:47 Verknüpfungstabelle AND Abfrage

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!
theo
 
Beiträge: 8255
Registriert: 11. Sep 2006, 18:01

Beitragvon sstvmaster » 15. Okt 2019, 14:54 Re: Verknüpfungstabelle AND Abfrage

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
sstvmaster
 
Beiträge: 258
Registriert: 22. Okt 2016, 22:12
Wohnort: Dresden
OS, Lazarus, FPC: OS: Windows 10 | Lazarus: 2.0.6 + Fixes + Trunk 32bit | 
CPU-Target: 32Bit
Nach oben

Beitragvon Warf » 15. Okt 2019, 15:47 Re: Verknüpfungstabelle AND Abfrage

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)
Warf
 
Beiträge: 1374
Registriert: 23. Sep 2014, 16:46
Wohnort: Aachen
OS, Lazarus, FPC: MacOS | Win 10 | Linux | 
CPU-Target: x86_64
Nach oben

Beitragvon theo » 15. Okt 2019, 15:50 Re: Verknüpfungstabelle AND Abfrage

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?
theo
 
Beiträge: 8255
Registriert: 11. Sep 2006, 18:01

Beitragvon theo » 15. Okt 2019, 15:52 Re: Verknüpfungstabelle AND Abfrage

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!
theo
 
Beiträge: 8255
Registriert: 11. Sep 2006, 18:01

Beitragvon af0815 » 15. Okt 2019, 16:44 Re: Verknüpfungstabelle AND Abfrage

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).
af0815
 
Beiträge: 3939
Registriert: 7. Jan 2007, 10:20
Wohnort: Niederösterreich
OS, Lazarus, FPC: FPC 3.2 Lazarus 2.0 per fpcupdeluxe | 
CPU-Target: 32Bit (64Bit)
Nach oben

Beitragvon theo » 15. Okt 2019, 17:28 Re: Verknüpfungstabelle AND Abfrage

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")
theo
 
Beiträge: 8255
Registriert: 11. Sep 2006, 18:01

Beitragvon af0815 » 26. Okt 2019, 08:29 Re: Verknüpfungstabelle AND Abfrage

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 26. Okt 2019, 08:33, insgesamt 1-mal geändert.
Blöd kann man ruhig sein, nur zu Helfen muss man sich wissen (oder nachsehen in LazInfos/LazSnippets).
af0815
 
Beiträge: 3939
Registriert: 7. Jan 2007, 10:20
Wohnort: Niederösterreich
OS, Lazarus, FPC: FPC 3.2 Lazarus 2.0 per fpcupdeluxe | 
CPU-Target: 32Bit (64Bit)
Nach oben

Beitragvon theo » 26. Okt 2019, 10:02 Re: Verknüpfungstabelle AND Abfrage

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.
theo
 
Beiträge: 8255
Registriert: 11. Sep 2006, 18:01

Beitragvon af0815 » 26. Okt 2019, 13:24 Re: Verknüpfungstabelle AND Abfrage

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) 80-mal heruntergeladen
Blöd kann man ruhig sein, nur zu Helfen muss man sich wissen (oder nachsehen in LazInfos/LazSnippets).
af0815
 
Beiträge: 3939
Registriert: 7. Jan 2007, 10:20
Wohnort: Niederösterreich
OS, Lazarus, FPC: FPC 3.2 Lazarus 2.0 per fpcupdeluxe | 
CPU-Target: 32Bit (64Bit)
Nach oben

Beitragvon af0815 » 26. Okt 2019, 13:42 Re: Verknüpfungstabelle AND Abfrage

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).
af0815
 
Beiträge: 3939
Registriert: 7. Jan 2007, 10:20
Wohnort: Niederösterreich
OS, Lazarus, FPC: FPC 3.2 Lazarus 2.0 per fpcupdeluxe | 
CPU-Target: 32Bit (64Bit)
Nach oben

Beitragvon af0815 » 26. Okt 2019, 13:55 Re: Verknüpfungstabelle AND Abfrage

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).
af0815
 
Beiträge: 3939
Registriert: 7. Jan 2007, 10:20
Wohnort: Niederösterreich
OS, Lazarus, FPC: FPC 3.2 Lazarus 2.0 per fpcupdeluxe | 
CPU-Target: 32Bit (64Bit)
Nach oben

Beitragvon theo » 26. Okt 2019, 21:01 Re: Verknüpfungstabelle AND Abfrage

@af0815: Danke! Das schaue ich mir morgen noch genauer an.
theo
 
Beiträge: 8255
Registriert: 11. Sep 2006, 18:01

Beitragvon af0815 » 27. Okt 2019, 06:04 Re: Verknüpfungstabelle AND Abfrage

Ü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).
af0815
 
Beiträge: 3939
Registriert: 7. Jan 2007, 10:20
Wohnort: Niederösterreich
OS, Lazarus, FPC: FPC 3.2 Lazarus 2.0 per fpcupdeluxe | 
CPU-Target: 32Bit (64Bit)
Nach oben

Beitragvon theo » 27. Okt 2019, 10:25 Re: Verknüpfungstabelle AND Abfrage

@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!
theo
 
Beiträge: 8255
Registriert: 11. Sep 2006, 18:01

» Weitere Beiträge siehe nächste Seite »
Nächste

Zurück zu Datenbanken



Wer ist online?

Mitglieder in diesem Forum: 0 Mitglieder und 2 Gäste

porpoises-institution
accuracy-worried