Feb 14, 2012

19505

MySQL Suche über mehrere Tabellen

Hiho Hiho,

wer die Überschrift liest, wird erstmal merken "da gibts eine Menge Stoff zu". Stimmt, aber irgendwie haut bei mir was nicht hin

Zum Problem: Ich habe eine MySQL Datenbank mit 4 Tabellen. Diese sind (fast) gleich aufgebaut,

ID | Titel | weitere Felder mit Informationen...

die Abweichung besteht eigentlich nur in der Anzahl der Felder.

Nun will ich eine einfache Suche ausführen lassen. User kommt also auf die Webseite und gibt Suchbegriff X ein, dieser soll dann brav aufgelistet werden. Im konkreten Beispiel wäre das eine Suche nach einem Filmtitel, der in einer oder mehreren Tabellen vorkommt. Mein Statement funktioniert aber nicht und ich sehe den Fehler nicht. Habe es auch schon oft geändert, aber nie das gewünschte Ergebnis erzielt. Der momentane Stand ist folgender:

Titel A steht in allen Tabellen und wird auch in allen gefunden.
Titel B steht nur in Tabelle 2, wird aber nicht gefunden. Komischerweise steht ja in Tabelle 2 auch Titel A, dieser wird gefunden.
Titel C steht in Tabelle 3, wird auch nicht gefunden. Titel A wird hier gefunden.
Titel D steht in Tabelle 4 und wird auch gefunden, aber nicht, wenn ich diesen auch in Tabelle 1 eintrage.

Mir gehen hier echt die Ideen aus. Ich bin für jede Antwort dankbar, die mich irgendwie weiterbringen könnte.

SELECT
tbl1.id AS table_one_id, tbl1.titel AS table_one_titel, tbl2.id AS table_two_id, tbl2.titel AS table_two_titel, tbl3.id AS table_three_id, tbl3.titel AS table_three_titel, tbl4.id AS table_four_id, tbl4.titel AS table_four_titel FROM
tabelle1 tbl1 RIGHT JOIN tabelle2 tbl2 ON tbl1.titel = tbl2.titel RIGHT JOIN tabelle3 tbl3 ON tbl1.titel ORt bl2.titel = tbl3.titel RIGHT JOIN tabelle4 tbl4 ON tbl1.titel OR tbl2.titel OR tbl3.titel = tbl4.titel WHERE tbl1.titel OR tbl2.titel OR tbl3.titel OR tbl4.titel = 'Titel B'

Mfg
Mitchell

Please also mark the comments that contributed to the solution of the article

Content-Key: 180542

Url: https://administrator.de/contentid/180542

Printed on: April 24, 2024 at 22:04 o'clock

19 Comments

Latest comment

Über die Datenmodellierung der Datenbank will ich jetzt erstmal kein Wort verlieren, die verkompliziert das ganze aber deutlich.

Also so wie ich das sehe, ist Join auch die völlig falsche Vorgehensweise.
Wenn ich das richtig verstehe, sind die 4 Tabellen praktisch unabhängig voneinander. Wozu dann ein Join der Tabellen untereinander?
Wenn ich unabhängige Tabellen habe und die in einer Ausgabe zusammenführen will, ist eine SQL Union das richtige.
Also etwas in der Art (falls MySQL union kennt):
select ID, Titel from table_one_titel where Titel='Titel B'
union
select ID, Titel from table_two_titel where Titel='Titel B'
union
select ID, Titel from table_three_titel where Titel='Titel B'
union
select ID, Titel from table_four_titel where Titel='Titel B'

Über die Datenmodellierung der Datenbank will ich jetzt erstmal kein Wort verlieren, die verkompliziert das ganze aber
deutlich.

lass dich ruhig aus, ich lerne gerne dazu

Wenn ich das richtig verstehe, sind die 4 Tabellen praktisch unabhängig voneinander. Wozu dann ein Join der Tabellen
untereinander?

weil ich die Tabellen ja miteinander verknüpfen muss, damit auch in jeder gesucht wird. Wirst du in jedem Handbuch/Tutorial so finden.

Also etwas in der Art (falls MySQL union kennt):

ist hier das falsche Mittel, da mir UNION die doppelten Datensätze rausfiltert und mit UNION ALL komme ich auch nicht viel weiter. Und ja, MySQL kennt UNION...

Mfg
Mitchell

Hallo Mitchell,

also UNION hört sich eigentlich ganz gut an. Und wenn UNION ALL die doppelten Datensätze rausfiltert, dann hat es irgendwie seine Funktion verfehlt. Aber um zu wissen, aus welcher Tabelle der Eintrag stammt, könntest Du auch noch eine Konstante 'Tabelle x' AS Tabelle in jede der vier Teilabfragen einbauen, dann kannst Du schwer überhaupt noch Dubletten haben.

Falls Du aber unbedingt mit joins zum Ziel kommen willst, dann solltest Du kein RIGHT JOIN, sondern eher ein FULL JOIN verwenden.

Gruß, Mad Max

weil ich die Tabellen ja miteinander verknüpfen muss, damit auch in jeder gesucht wird. Wirst du in jedem Handbuch/Tutorial so finden.

Nein, wird man so nicht finden. Fremdschlüsselbeziehungen setzen voraus, daß die Inhalte der Felder auch eine Beziehung haben.
Bei Dir sind zufällig in den 4 Tabellen in jeweils einer Spalte ein Wert drin den Du suchen möchtest. Das ist keine Schlüsselbeziehung.

Wenn Du die Info brauchst, in welcher Tabelle der Wert gefunden wird, dann Union mit Angabe der Tabelle wie von Mad Max vorgeschlagen.

Oder, anderer Vorschlag:
Mach eine View über alle Titelspalten der 4 Tabellen (mit Union)
Damit ist sichergestelt, daß alle Titel drin sind.
Und dann einen Join aller Tabellen gegen diese View. Da alle Werte in der View drin sind, kann man auch zu jeder Zeile dort einen Join gegen die Werte der anderen Tabellen ausführen.

@max: UNION ALL zeigt mir natürlich dopellte Datensätze, UNION halt nur nicht...das war damit gemeint

FULL JOIN gibt es in MySQL übrigens nicht, dafür ist UNION ALL dann da

@andreas: die Beziehung wird doch durch den Schlüssel ID hergestellt, oder versteh ich da was falsch? Deinen letzten Vorschlag verstehe ich gerade nicht so ganz, aber kommen wir doch mal auf das UNION zurück. Ich habe das schon probiert (vor den JOINs, da mir UNION auch logischer erschien). Ein Kollege hat mir nur gesagt, dass es a) auch mit JOINs geht - war also zum einen der Versuch - und b) UNION im Gegensatz zu JOIN einen Nachteil in der Performance hat.

Aber gut, ich poste euch mal mein Ursprungsstatement, bei dem ich allerdings auch irgendwas falsch mache. Es passiert hier nämlich folgendes:
- Es wird alles gefunden, egal, in welcher Tabelle und natürlich auch die Dubletten (da kam erstmal Freude bei mir auf, bis ich näher hingeschaut habe)
- Er zeigt mir alles als tbl1 an. Ich suche also zum Beispiel nach "Titel A", der in allen Spalten vorkommt. In der Ausgabe gibt er mir dann allerdings 4 Funde zurück, die alle in "tbl1" stehen...und da begann mein großes Fragezeichen, auch deswegen bin ich zu den JOiNs übergegangen. Statement:

(SELECT id AS tbl1_id, titel AS tbl1_titel FROM tabelle_1 WHERE titel = 'Titel A')
UNION ALL
(SELECT id AS tbl2_id, titel AS tbl2_titel FROM tabelle_2 WHERE titel = 'Titel A')
UNION ALL
(SELECT id AS tbl3_id, titel AS tbl_3titel FROM tabelle_3 WHERE titel = 'Titel A')
UNION ALL
(SELECT id AS tbl4_id, titel AS tbl_4titel FROM tabelle_4 WHERE titel = 'Titel A')

Mfg
Mitchell

mit 4 Tabellen. Diese sind (fast) gleich aufgebaut,

da ist es wohl durchaus sinnvoll für jede Tabelle eine spezialisierte SQL Anweisung für die Suche zu schreiben - und die Ergebnisse dann zurück zu geben.

Statement:

mich wundert, dass dein Statement überhaupt ohne Fehlermeldung ausgeführt wird, da die Feldnamen nicht überein stimmen.

SELECT `id`, `titel`, 'table_1' AS 'table' FROM `table_1` WHERE `titel` = 'Titel A'  
UNION
SELECT `id`, `titel`, 'table_2' AS 'table' FROM `table_2` WHERE `titel` = 'Titel A'  
UNION
SELECT `id`, `titel`, 'table_3' AS 'table' FROM `table_3` WHERE `titel` = 'Titel A'  
UNION
SELECT `id`, `titel`, 'table_4' AS 'table' FROM `table_4` WHERE `titel` = 'Titel A'  

... um das Ergebnis noch Sortieren zu können wäre eine Art Ranking evtl. noch sinnvoll.

Ich frage mich aber: Warum sind die Daten in verschiedenen Tabellen ? Wäre es evtl sinnvoll ein View mit allen Daten zu erstellen - oder gar eine separate Tabelle ?

die Beziehung wird doch durch den Schlüssel ID hergestellt

Das würde aber bedeuten, daß in Tabelle 1 die Zeile ID 1 und in Tabelle 2/3/4 die Zeile mit ID 1 eine Beziehung miteinander haben. Der Aussage, daß die Titel in allen vorkommen können, aber nicht müssen, sagt doch eigentlich, daß dem nicht so ist.
Nur weil Spalten gleich heißen, hat das noch nichts mit Beziehungen zu tun.

Um auf den Kommentar von nxclass zurückzukommen, eine Tabelle mit allen Titeln wäre vermutlich eine sinnvollere Datenmodellierung, die unterschiedliche Anzahl an Datenspalten kann man dann ja über separate Tabellen abwickeln, die man über Beziehungen und Join miteinander verbindet.

Aber schau Dir mal das Union Statement von nxclass an, daß wird Dir vermutlich weiterhelfen.

Er zeigt mir alles als tbl1 an

Was soll er denn bei deinem Union sonst machen? Union packt alle Ergebnisse in EIN Recordset. Und Spalten haben genau EINEN Titel. Wo soll da die Info herkommen, in welcher Tabelle es steht?

@nxclass:

mich wundert, dass dein Statement überhaupt ohne Fehlermeldung ausgeführt wird, da die Feldnamen nicht überein stimmen.

wieso, die stimmen doch überein...bekommen halt nur ein Alias?!

SELECT `id`, `titel`, 'table_1' AS 'table' FROM `table_1` WHERE `titel` = 'Titel A'...

verwirrt mich ein wenig. Du willst, dass ich dem Feld "table_1 ein Alias gebe? Das Feld existiert nicht. Meinst du vielleicht ein Alias für die Tabelle "...FROM 'table_1' table?

Ich frage mich aber: Warum sind die Daten in verschiedenen Tabellen ?

weil mir ein Freund dazu geraten hat wegen der Übersichtlichkeit. Ich hatte die Daten vorher in einer Tabelle.

@andreas:

eine Tabelle mit allen Titeln wäre vermutlich eine sinnvollere Datenmodellierung, die unterschiedliche Anzahl an Datenspalten kann man dann ja über separate Tabellen abwickeln, die man über Beziehungen und Join miteinander verbindet.

stünde ich dann nicht vor dem selben Problem, wie jetzt? Mit dem Unterschied, dass ich nicht über 4 Tabellen muss, sondern über 10.

Was soll er denn bei deinem Union sonst machen? Union packt alle Ergebnisse in EIN Recordset.

hm...ok. Dann müsste ich quasi, um 4 Sets zurück zu bekommen, den JOIN dazwischen packen, oder? Sowas in der Richtung

Mfg
Mitchell

stünde ich dann nicht vor dem selben Problem, wie jetzt? Mit dem Unterschied, dass ich nicht über 4 Tabellen muss, sondern über 10.

Nein, weil der Suchbegriff in einer vorkommt (damit nur eine where Bedingung) und wir Beziehungen zu den anderen Tabellen haben, die für einen Join geeignet sind.

verwirrt mich ein wenig. Du willst, dass ich dem Feld "table_1 ein Alias gebe? Das Feld existiert nicht. Meinst du vielleicht ein Alias für die Tabelle "...FROM 'table_1' table?

Kein Feld, es ist eine Textkonstante.
Du hast Dich ja beschwert, daß in Deinem Union Du nicht mehr erkennen kannst, aus welcher Tabelle der Treffer kommt.
Deswegen schlägt nxclass vor, daß du je nach Tabelle eine Textkonstante table_1, table_2 etc. der Daten-Zeile hinzufügst, damit Du hinterher erkennen kannst, aus welcher Tabelle der Treffer kommt.

Und wenn man mit SQL auf Kriegsfuss steht, gibt es immer noch die Möglichkeit in PHP oder womit die Webseite auch immer zusammengebastelt ist 4 Selects abzusetzen und es im Programm zusammenzusetzen.

Nein, weil der Suchbegriff in einer vorkommt (damit nur eine where Bedingung) und wir Beziehungen zu den anderen Tabellen haben, die für einen Join geeignet sind.

ok, leuchtet ein

Aber bevor ich jetzt meine Datenbank noch 3x umstelle...was haltet ihr denn für besser? Die vorgeschlagene Variante mit der Aufteilung in mehrere Tabellen (also Titel in tbl1, Infos in tbl_2, etc.) oder eine einzige Tabelle, die dann halt noch ein Abfragefeld bekommt?

Deswegen schlägt nxclass vor, daß du je nach Tabelle eine Textkonstante table_1, table_2 etc. der Daten-Zeile hinzufügst, damit Du hinterher erkennen kannst, aus welcher Tabelle der Treffer kommt.

genau das hat mich irritiert, ich dachte, er wolle die Tabelle halt nochmal benennen. Also "...FROM table_1 tabelle..." und hat sich einfach vertippt

...4 Selects abzusetzen und es im Programm zusammenzusetzen.

Das kommt noch dazu. Mittels switch-case wird eine Liste erstellt...falls aus tbl_1, tu das, falls tbl_2, tu das usw.

Jedenfalls schonmal danke für eure Hile bis hierher. Ich schaue evtl. nachher nochmal hier rein, bin aber erstmal bis Mitte nächste Woche nicht da und werde dann eure Vorschläge testen.

Mfg
Mitchell

... weil mir ein Freund dazu geraten hat wegen der Übersichtlichkeit. Ich hatte die Daten vorher in einer Tabelle.
... was haltet ihr denn für besser?

weder dein Freund noch Du brachst "Übersicht" über deine Daten - dafür ist die Datenbank da.

Alles in eine Tabelle zu packen ist möglich aber für MySql nicht sinnvoll.
Überlege doch mal was zusammen gehören könnte: Beispiel

ein Film hat einen Titel, Genre, ...
ein Film hat mehrere Eigenschaften wie Länge, Beschreibung, etc

Tabelle: 'filme' - Felder: 'id_film, titel, genre, ...'  
Tabelle: 'eigenschaften' - Felder: 'id_eigenschaft, eigenschaft'  
Tabelle: 'filmeigenschaften' - Felder: 'id_film, id_eigenschaft, eigenschaft_wert'  

so kannst du zu jedem Film, beliebig viele Eigenschaften anlegen, und Du kannst rel. einfach über die Tabellen Suchen und JOINs ausführen

bin zwar noch nicht zu Hause, aber schonmal für morgen oder so

:

Wieso wäre es für MySQL nicht sinnvoll, alles in eine Tabelle zu schreiben? Ok, wären einige Felder (an die 15), aber die Datenbank müsste dann doch nicht in mehreren Tabellen suchen, sich mit JOINs rumschlagen (einfach weniger Abfragen machen), etc....sondern nur in einer Tabelle nachgucken. Wäre das von der Perfomance her nicht besser?

Mfg
Mitchell

die Datenbank müsste dann doch nicht in mehreren Tabellen suchen, sich mit JOINs rumschlagen

MySql ist aber genau dafür optimiert - und mit meinem Beispiel kannst Du die Suche mit einer Anweisung ausführen.

müsste dann doch nicht in mehreren Tabellen suchen

die eigentliche Suche sollte im besten Fall auch gar nicht in der Tabelle stattfinden - dafür erzeugt man einen Index in der Tabelle filme über die Spalten id_film und titel

Es gibt dann noch andere Tricks um die Suche zu beschleunigen.

hm...ok. Ich werds mal probieren, die Tabellen umstellen und eventuell heute noch verssuchen, das ganze zu testen. Wie du bemerkt hast, sind JOINs nicht so mein Fall, aber...mühsam ernährt sich das Eichhörnchen

Danke nochmal an euch, vorallem nxclass. Ich gebe Feedback, sobald ich was hinbekommen habe (oder Frage nach, falls nicht :-P).

Mfg
Mitchell

Sorry für die späte Meldung, kam was dazwischen.

Also ich habe hier mal was getestet, was deinem Vorschlag (nxclass) nahekommen sollte, zumindest habe ich es so verstanden. Die vielen Felder habe ich nun in Tabellen untergebracht, die meisten jedenfalls. Jede Tabelle hat nun ein Zusatzfeld "feld_abfrage", mit dem ich dann nach dem Titel suche. Klappt auch soweit, nur zwei Dinge fallen mir hier auf.

1. Ich wusste gar nicht, dass jede Tabelle die gleiche Anzahl an Feldern haben muss.
2. Ich bekomme nun zwar mein gewünschtes Resultat, alterdings im Format zweier Spalten. Mir wird der Titel angezeigt und das Genre...unter Titel stehen nun aber auch die Eigenschaften.

Ich mache was falsch, das ist mir bewusst

Nur weiß ich nicht genau, was...ich hasse diese Verknüfungsorgien. Statement (gekürzt, die Abfragen sind, bis auf die Tabellen -und Feldnamen, gleich):

(SELECT titel, genre FROM tbl1 WHERE titel = 'Titel A')
UNION ALL
(SELECT names AS d_names, d_search FROM tbl2 WHERE d_abfrage = 'Titel A')
UNION ALL
...
(SELECT user, u_abfrage FROM nutzer WHERE u_abfrage = 'Titel A')

Ausgabe ergibt dann (nicht wundern, es ist 2x Titel A vorhanden, das ist also korrekt):

Hoffe, damit ist was anzufangen.

Mfg
Mitchell

Bevor ich hier wieder was umstelle und rumbastel, wüsste ich dann doch gerne, welchen Vorteil/Nachteil folgende Methoden haben:

1. Ich speichere sämtliche Daten in einer Tabelle, diese besteht dann aus ca. 10 Feldern
2. Ich nutze mehrere Tabellen und verteile die Felder, also eine Tabelle für "titel", eine für "info 1", "info 2", usw.

Ich sehe den Nutzen bzw. Vorteil bei der Suche einfach nicht. Die Geschwindigkeit kanns nicht sein, was also dann?

Mfg
Mitchell

nahekommen sollte, zumindest habe ich es so verstanden

moment - Bei einer Suche:

SELECT * FROM tbl1 WHERE titel LIKE '%{suchstring}%';

... dann bekommst du eine Liste mit Ergebnissen (u.A. der Film IDs) anhand Du weitere Informationen nachladen kannst mit:

SELECT tbl2.eigenschaft_wert, tbl3.eigenschaft
FROM tbl2 JOIN tbl3 ON tbl2.id_eigenschaft = tbl3.id
WHERE tbl2.id_film = {FILM_ID}

... nun hast du zu dem Film eine Liste mit Key-Values

tbl1: Filme
tbl2: Film_Eigenschaften
tbl3: Eigenschaften

Dieser Vorschlag ist insofern Vorteilhaft, wenn nicht jeder Film die selbe Anzahl Eigenschaften besitzt.
Zu den Eigenschaften kann man natürlich noch den Titel und den Genre dazu nehmen - dann kann man mit einem Suchbegriff über das Feld 'eigenschaft_wert' eigentlich alles Finden.

Eine Suche wird mit der Zeit sehr langsam werden. Lässt sich umständlich erweitern - also nur durch ändern der Tabellen Struktur. Sehe da keine Vorteile

Eine Datenabfrage wird dadurch sehr umständlich. Sowas kann man zB. mit Beschreibungs Texten machen, diese könnte man ich eine extra Tabelle packen.

Hi nxclass,

so ungefähr habe ich es auch gemacht, aber schon bevor du deinen Vorschlag gepostet hast. Ich konnts mir aber durch die vorherigen Posts dann doch denken, daher bedanke ich mich nochmal recht herzlich. Viel Arbeit hätte mir erspart bleiben können, wenn ich es direkt so (wie von dir und den anderen geschrieben) angefangen hätte. Naja, man lernt draus. Vorallem komme ich jetzt mit den JOINs auch klar ^^.

Lösung sieht also jetzt wie folgt aus:

Datenbank hat mehrere Tabellen mit 2 - 5 Feldern, statt nur einer Tabelle mit 10 - 13 Feldern. Abfrage ist dementsprechend lang, ich kürze sie hier daher ein wenig (hinter dem UNION SELECT kommen im Grunde die selben SELECTs mit dem Unterschied, dass jeweils die JOINs umgedreht werden. Damit bekommt man in MySQL den FULL JOIN, es werden also wirklich alle Datensätze erfasst):

SELECT tbl1.id AS tbl_one_id, tbl1.titel, tbl2.namen, tbl3.date, tbl4.info
FROM tabelle_1 tbl1
LEFT JOIN tabelle_3 tbl3 ON ( tbl1.id = tbl3.id )
LEFT JOIN tabelle_2 tbl2 ON ( tbl1.id = tbl2.id )
RIGHT JOIN tabelle_4 tbl4 ON ( tbl1.id = tbl4.id )
WHERE tbl1.titel LIKE '%$begriff%'
UNION SELECT...

German solved Question Databases Development

Hotly discussed

Check of ZFW Firewallgleixnerd - 5 Comments

Wireguard VPN on UDM Pro behind Fritzbox - Handshake did not completejstricker - 3 Comments