Select Abfrage bei n:m Beziehung

[BennoGronau]

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

 

[Hony%]

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:

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

 

[BerndB]

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.

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;

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

 

[akretschmer]

Sollte doch eigentlich ein Standardproblem sein, oder?

Vielen Dank schon mal im Voraus für alles Mitdenken und alle Antworten

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:

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:

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.