1. Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm
    Information ausblenden

Multiple Joins über mehrere Tabellen

Dieses Thema im Forum "MySQL und MariaDB" wurde erstellt von Aljoscha.Peters, 5 September 2015.

  1. Aljoscha.Peters

    Aljoscha.Peters Neuer Benutzer

    Hi Leute,

    ich arbeite gerade an meiner Abschlussarbeit. Diese basiert u.a. auf einer MySQL-Datenbank. Diese ist ordentlich normalisiert. Nun brauche ich eine Tabelle, die von mehreren Tabellen befüllt wird. Woran es scheitert, sind die JOINS. Da setzt mein Kopf gerade aus und deshalb baue ich auf Eure Hilfe. O:)

    Ich habe 5 Tabellen: entities, keywords, names, entities_keywords, entities_authors; die sind wie folgt aufgebaut:

    entities: id : int, title : text, ..., trash : tinyint
    keywords: id : int, title : text, trash : tinyint
    names: id : int, title : text, trash : tinyint
    entities_keywords: entities_id : int, keywords_id : int (zusammengesetzter primary index aus entities_id und keywords_id)
    entities_authors: entities_id : int, names_id : int, position : int

    Die normalen Verknüpfungen (n-m-Relation) zur Anzeige funktionieren wunderbar. Updaten/Löschen ... alles super.

    Jetzt brauche ich für die Realisierung der Suche folgendes:
    Ich habe eine neue Tabelle erstellt (search_entities_authors_keywords), die erst einmal - um es zu vestehen - befüllt werden soll mit den Daten der m-n-Relationen von oben. Bisher bin nur erfolgreich bei Tabellen wie bspw. (search_entities_keywords), die dann folgendes machen:
    INSERT `search_entities_keywords` (`id`, `title`, `year`,`keywords`
    SELECT `e`.`id`, `e`.`title`,`e`.`year`, GROUP_CONCAT(`k`.`title` ORDER BY `k`.`title` SEPARATOR ',')
    FROM
    ((`entities_keywords`
    LEFT JOIN `keywords` `k` ON ((`entities_keywords`.`keywords_id` = `k`.`id`)))
    LEFT JOIN `entities` `e` ON ((`entities_keywords`.`entities_id` = `e`.`id`)))
    WHERE `e`.`trash` = 0 AND `k`.`trash` = 0
    GROUP BY `slipbox`.`entities_keywords`.`entities_id`
    ORDER BY `e`.`title` ASC

    Mir ist also unklar, wie ich es anstellen muss, dass ich ALLE entities bekomme (mit e.trash=0), mit allen Keywords und Authors!

    Ich muss ja eigentlich dann SELECT ... FROM entities nutzen mit mehreren JOINS über die Verknüpfungstabellen entities_keywords und entities_authors. Aber wie ich das mache, ist mir total schleierhaft! :(

    Ich danke Euch im Voraus schon einmal gaaaaanz herzlich und wünsche weiterhin ein schönes Wochenende.
    Aljoscha
     
  2. akretschmer

    akretschmer Datenbank-Guru

    Wozu benötigst Du eine neue Tabelle für die Suche? Da reicht eine Abfrage, oder ein VIEW.

    Zeig Dein Problem anhand der Tabellendefinitionen, einigen Beispiel-Datensätzen und dem, was rauskommen soll.

    Was ich schon sehe: Du aggregierst ohne zu gruppieren. Auch wenn MySQL für den Fehler blind ist: es ist ein Fehler.
     
  3. Aljoscha.Peters

    Aljoscha.Peters Neuer Benutzer

    Hi akretschmer!

    Danke zuallererst für Dein Hilfsangebot! :)
    Habe einen Teil der Tabellendefinitionen als UML angehängt. Hinzu kommen noch weitere Tabellen für Ideen, Sammlungen, Dateien, ... Wenn ich aber das Prinzip mit den JOINS für das Problem verstanden hab, komme ich mit dem Rest sicherlich allein klar. O:)

    Vorweg:
    Mein theoretisches Thema zur BA beschäftigt sich leider garnicht mit Information Retrieval oder Datenbanken, deshalb meine Unwissenheit.

    Ich hatte im Netz aufgeschnappt, jetzt, wo sich das Problem der Suche stellt, dass Views nicht gecached werden. Ziel ist es aber gerade, eine möglichst schnelle Suche zu bekommen. Deshalb meine Idee: Da die Datensätze eher selten geändert werden, eher werden ab und an neue Hinzugefügt (Zettel- und Ideenkasten eines Profs.), einfach eine Tabelle (oder mehrere Tabellen), die gerade die spezifischen Suchanfragen des Profs. berücksichtigen. Zudem kann ich dann über die Tabelle(n je) eine FULLTEXT-Suche legen.

    Zudem habe ich schon mit Views gespielt und ich fand die Geschwindigkeit nicht so pralle (Entitäten sind 100.000 Datensätze), dafür, dass ich solche "einfachen" Joins wie oben benutzte. Nutze die MySQL-Workbench.

    Am Server selbst kann ich nicht all zu viel ändern, es wird ein einfacher XAMPP von Apachefriends mit einem Windows 7 sein. Wahrscheinlich sogar als Portable-Version. UI wird über PHP-Framework Phalcon realisiert.

    Aber zurück zur anzulegenden Tabelle:
    Aus den sechs Tabellen soll eine herauskommen mit den Spalten:
    entities.id, entities.title, entities.subtitle, entities.year, group_concat(keywords.title SEPARATOR ',') - der jeweiligen Keywords, group_concat(names.title SEPARATOR ',') - der jeweiligen Namen, die als Autoren verwendet werden, group_concat(names.title SEPARATOR ',') - der jeweiligen Namen, die als Herausgeber (editors) verwendet werden.

    Bsp.:
    entities: (id = 1, title = 'Testentität', subtitle = NULL, year = 1970, ..., trash = 0)

    entities_authors: (entities_id = 1, names_id = 1)
    entities_editors: (entities_id = 1, names_id = 2), (entities_id = 1, names_id = 3)
    entities_keywords (entities_id = 1, keywords_id = 1), (entities_id = 1, keywords_id = 2), (entities_id = 1, keywords_id = 3)

    mit
    names: (id = 1, title = 'Name 01', trash = 0), (id = 2, title = 'Deleted name', trash = 1), (id = 3, title = 'Name 03', trash = 0), ...
    keywords: (id = 1, title = 'Keyword 01', trash = 0), (id = 2, title = 'Keyword 02', trash = 0), (id = 3, title = 'Deleted keyword', trash = 1), ...

    Herauskommen soll in der Tabelle:
    id = 1, title = Testentität, subtitle = NULL, year = 1970, keywords = 'Keyword 01,Keyword 02', authors = 'Name 01', editors = 'Name 03'

    Da die Daten eher selten geändert werden, würde ich dann Trigger hinterlegen:
    Wenn Entität erstellt wird, füge entsprechende Zeile ein.
    Wenn Entität geändert wird, ändere entsprechende Zeile.
    Wenn Entität gelöscht wird, löschen entsprechende Zeile.
    Wenn Name geändert wird, ändere entsprechende Spalten.
    Wenn Name gelöscht wird, ändere entsprechende Spalten.
    ...

    Danke Dir nochmals!
     

    Anhänge:

  4. akretschmer

    akretschmer Datenbank-Guru

    Um es mal ganz simpel zu zeigen:

    Code:
    test=*# create table autor (id int primary key, name text);
    CREATE TABLE   
    Time: 26,757 ms   
    test=*# create table buch (id int primary key, name text);
    CREATE TABLE   
    Time: 18,276 ms   
    test=*# create table buch_autor (buch_id int references buch, autor_id int references autor, primary key(buch_id, autor_id));
    CREATE TABLE   
    Time: 81,758 ms   
    test=*# insert into autor values (1, 'autor1');
    INSERT 0 1   
    Time: 0,434 ms   
    test=*# insert into autor values (2, 'autor2');
    INSERT 0 1   
    Time: 0,200 ms   
    test=*# insert into autor values (3, 'autor3');
    INSERT 0 1   
    Time: 0,192 ms   
    test=*# insert into buch values (1, 'buch1');  
    INSERT 0 1   
    Time: 0,479 ms   
    test=*# insert into buch values (2, 'buch2');
    INSERT 0 1   
    Time: 0,188 ms   
    test=*# insert into buch_autor values (1,1);  
    INSERT 0 1   
    Time: 11,834 ms   
    test=*# insert into buch_autor values (1,2);
    INSERT 0 1
    Time: 0,354 ms
    test=*# insert into buch_autor values (1,3);
    INSERT 0 1
    Time: 0,310 ms
    test=*# insert into buch_autor values (2,3);
    INSERT 0 1
    Time: 0,326 ms
    test=*# insert into buch_autor values (2,2);
    INSERT 0 1
    Time: 0,368 ms
    test=*# select b.name, array_agg(a.name) from buch_autor ab left join autor a on ab.autor_id=a.id left join buch b on ab.buch_id=b.id group by b.name;
     name  |  array_agg
    -------+------------------------
     buch2 | {autor3,autor2}
     buch1 | {autor1,autor2,autor3}
    (2 rows)
    
    Das ist jetzt PostgtreSQL, das Resultat ein ARRAY.

    Code:
    test=*# select b.name, array_to_string(array_agg(a.name),', ') from buch_autor ab left join autor a on ab.autor_id=a.id left join buch b on ab.buch_id=b.id group by b.name;
     name  |  array_to_string
    -------+------------------------
     buch2 | autor3, autor2
     buch1 | autor1, autor2, autor3
    (2 rows)
    
    So ists besser. Wichtig ist das GROUP BY, ich denke, das Prinzip eines JOIN's sollte erkennbar sein.

    Views und lahm: richtige Datenbanken haben materialized views. Du hast MySQL. Das hat wohl auch keine so schnellen Joins. Kann man machen mit triggern, ist aber ein weiteres Feld von Fehlern.
     
  5. Aljoscha.Peters

    Aljoscha.Peters Neuer Benutzer

    Hey, danke für die Mühe!

    Einen einmaligen JOIN kriege ich hin, auch mit GROUP BY mache ich das. Woran ich scheitere - da bin ich wahrscheinlich einfach zu blöd bin: Ich will ja gerade in einer Tabelle Entitäten-ID, Entitäten-TITEL und Keywords und Autoren und Herausgeber. Da muss ich doch bestimmt erst andere JOINS machen, mir ist aber unklar, welche und in welcher Reihenfolge. :/ Oder muss ich sogar mit Subqueries arbeiten?!

    Danke Dir
     
  6. akretschmer

    akretschmer Datenbank-Guru

  7. norbert_pdm

    norbert_pdm Benutzer

    Hallo, hallo....

    Danke für die Beispiele und die Erläuterungen!
    Ich bin kein Datenbankexperte (wie unschwer zu erahnen ist ;-) ), hab das aber mal mit meinen Tabellen und in MySQL nachgebaut (und sogar ein bisschen verstanden).

    Allerdings stolpere ich (natürlich, da PostgreeSQL) über
    array_to_string(array_agg(a.name),', ')

    Wie müsste das denn für MySQL heißen?
    Ich finde im Netz (wahrscheinlich dank unklarer Suchen) leider keinen nachvollziehbaren Tipp.


    VG, Norbert
     
  8. akretschmer

    akretschmer Datenbank-Guru

    Simpel, genau so. Du mußt halt nur MySQL durch PostgreSQL ersetzen, schon klappt das.

    Ansonsten: MySQL kann halt nix. Du kannst halt weiter damit leben - und dann lebe halt damit. In diesem Falle kannst mit group_concat() (siehe MYSQL array aggregate function like PostgreSQL array_agg ) vielleicht noch was erreichen, aber letztendlich ist MySQL einfach nur Müll.
     
  9. norbert_pdm

    norbert_pdm Benutzer

    Hallo akretschmer,

    Danke für die Antwort! Damit konnte ich mein Problem (trotz mySQL ;-) ) lösen.

    VG, Norbert
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden