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

Select Abfrage bei n:m Beziehung

Dieses Thema im Forum "MySQL und MariaDB" wurde erstellt von BennoGronau, 14 November 2014.

  1. BennoGronau

    BennoGronau Neuer Benutzer

    Hi,
    ich möchte eine select abfrage erstellen, habe aber keine Idee wie. Es geht um 3 Tabellen, die eine n:m Beziehung darstellen:

    1. Tabelle: tbl_uebungen hat u.a. folgende Spalten:
    fld_uebungs_id (Primär Schlüssel)
    fld_uebungs_name (varchar)
    ....
    2. Tabelle tbl_kategorien
    fld_kategorie_id (Primär Schlüssel)
    fld_kategorie_name (varchar)

    3. Tabelle tbl_uebung_kategorie_relation
    fld_uebungs_id (Primärschlüssel)
    fld_kategorie_id (Promärschlüssel)

    Die select Abfrage soll mir alle Felder aus tbl_uebungen geben, die mehreren angegeben Kategorien zugeordnet sind.

    Beispiel:
    tbl_uebungen:
    fld_uebungs_id, fld_uebungs_name
    1, ueb_aaa
    2, ueb_bbb
    3, ueb_ccc
    4, ueb_ddd

    tbl_kategorien:
    fld_kategorie_id, fld_kategorie_name
    1, kat_aaa
    2, kat_bbb
    3, kat_ccc
    4, kat_ddd
    5, kat_eee

    tbl_uebung_kategorie_relation:
    fld_uebungs_id, fld_kategorie_id
    1, 1 (ueb_aaa hat Kategorie kat_aaa)
    1, 2 (ueb_aaa hat Kategorie kat_bbb)
    1, 3 (ueb_aaa hat Kategorie kat_ccc)
    2, 1 (ueb_bbb hat Kategorie kat_aaa)
    4, 1 (ueb_ddd hat Kategorie kat_aaa)
    4, 2 (ueb_ddd hat Kategorie kat_bbb)
    4, 3 (ueb_ddd hat Kategorie kat_ccc)

    Es sollen nun z.b. alle Übungen die Kategorie kat_aaa UND kat_bbb UND kat_ccc haben (Ergebnis wäre also ueb_aaa und ueb_ddd) ausgegeben werden.

    Sollte doch eigentlich ein Standardproblem sein, oder?

    Vielen Dank schon mal im Voraus für alles Mitdenken und alle Antworten
     
  2. Hony%

    Hony% Datenbank-Guru

    Hi,

    genau genommen ist das auch ein relativ simples Standardproblem. Allerdings fehlen MySQL die Werkzeuge, wie zum Beispiel CTE, um das auf elegante Art zu lösen. Daher sind dafür eine Reihe JOINS notwendig:
    Code:
    SELECT DISTINCT U.fld_uebungs_name
    FROM (
      SELECT *
      FROM  tbl_uebung_kategorie_relation
      NATURAL JOIN tbl_kategorien
    )  AS A
    INNER JOIN (
      SELECT *
      FROM  tbl_uebung_kategorie_relation
      NATURAL JOIN tbl_kategorien
    ) AS B
      ON A.fld_uebungs_id = B.fld_uebungs_id
    INNER JOIN (
      SELECT *
      FROM  tbl_uebung_kategorie_relation
      NATURAL JOIN tbl_kategorien
    )  AS C
      ON B.fld_uebungs_id = C.fld_uebungs_id
    INNER JOIN tbl_uebungen AS U
      ON A.fld_uebungs_id = U.fld_uebungs_id
    WHERE A.fld_kategorie_name = 'kat_aaa'
    AND B.fld_kategorie_name = 'kat_bbb'
    AND C.fld_kategorie_name = 'kat_ccc'
    
    Die Abfrage selbst funktioniert mit bis zu 3 Kategorien und benötigt für jede zusätzliche Kategorie einen weiteren JOIN. Deshalb musst du die Abfrage entweder dynamische erzeugen oder vorher festlegen wie viele Kategorien maximal abgefragt werden dürfen.

    Gruß
    Hony
     
  3. BerndB

    BerndB Datenbank-Guru

    Hallo Benno,

    so gehts auch. Dabei müssen alle Kategorienamen per OR angegeben werden und bei having mist du die Anzahl der kategorien angeben in der die Uebung enthalten sein muss. Da kannst du auch mit = arbeiten wenn die Übung genau N mal da sein muss.
    Code:
    SELECT ue.*, sum(1) AS anz_kategorien
    
    FROM uebungen ue
    LEFT JOIN uebung_kategorie_relation ukr ON ukr.uebungs_id = ue.uebungs_id
    LEFT JOIN kategorien k ON k.kategorie_id = ukr.kategorie_id
    
    WHERE k.kategorie_name = 'kat_aaa'
       OR k.kategorie_name = 'kat_bbb'
       OR k.kategorie_name = 'kat_ccc'
    
    GROUP BY ue.uebungs_name
    
    HAVING anz_kategorien >= 3;
    Code:
    MariaDB [select3]> SELECT ue.*, sum(1) AS anz_kategorien
        ->
        -> FROM uebungen ue
        -> LEFT JOIN uebung_kategorie_relation ukr ON ukr.uebungs_id = ue.uebungs_id
        -> LEFT JOIN kategorien k ON k.kategorie_id = ukr.kategorie_id
        ->
        -> WHERE k.kategorie_name = 'kat_aaa'
        ->    OR k.kategorie_name = 'kat_bbb'
        ->    OR k.kategorie_name = 'kat_ccc'
        ->
        -> GROUP BY ue.uebungs_name
        ->
        -> HAVING anz_kategorien >= 3;
    +------------+--------------+----------------+
    | uebungs_id | uebungs_name | anz_kategorien |
    +------------+--------------+----------------+
    |          1 | ueb_aaa      |              3 |
    |          4 | ueb_ddd      |              3 |
    +------------+--------------+----------------+
    2 rows in set (0.01 sec)
    
    MariaDB [select3]>

    Gruss

    Bernd
     
    BennoGronau und Hony% gefällt das.
  4. akretschmer

    akretschmer Datenbank-Guru

    Im Prinzip ja, aber MySQL fehlen da, wi andere schon sagten, die Mittel.

    In anderen DB's wie PostgreSQL hättest Du verschiedene Wege, einer wäre, über die Verknüpfungstabelle je Übung die Kategorien zu aggregieren:

    Code:
    test=*# select * from t_uk;
     u_id | k_id
    ------+------
      1 |  1
      1 |  2
      2 |  2
      2 |  3
      3 |  3
      1 |  3
    (6 rows)
    
    Time: 0,178 ms
    test=*# select u_id, array_agg(k_id) from t_uk group by u_id;
     u_id | array_agg
    ------+-----------
      1 | {1,2,3}
      2 | {2,3}
      3 | {3}
    (3 rows)
    
    Und nun alle die Übungen rausfiltern, die in den Kategorien 1,2,3 sind:

    Code:
    test=*# select * from (select u_id, array_agg(k_id) from t_uk group by u_id) foo where array_agg @> array[1,2,3];
     u_id | array_agg
    ------+-----------
      1 | {1,2,3}
    (1 row)
    
    Via CTE ginge das sicher auch, sogar SQL-Standard-konformer. Aber beide Wege kann MySQL nicht.
     
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