phpMyAdmin - SQL-Abfrage 2 Tabellen

[pehape]

Hallo, ich brauche Hilfe
Muss für die Schule eine Homepage programmieren, deren Inhalte nur über eine Datenbank eingegeben werden sollen. Ich verwende notepad++ Ich habe Zwei Tabellen -> Texte und Medien.
Und zwar möchte ich, dass auf einer Seite 1 Text erscheint und 2 Bilder.
Meine Tabellen mit Beispielinhalten (stimmt das von der Logik so? Kann man die beiden Tabellen überhaupt verbinden? ):

1. Tabelle "Texte"
Nr | Thema | Inhalt
1 | Instrumente | Hier steht ein Text drinnen...
2 | Anleitung | Hier steht ein TExt....

2. Tabelle "Media"
Nr| Beschreibung | Verweis
1 | Trompete | trompete.jpg
1 | Geige | geige.jpg
2 | Fidel |Fidel.jpg
3 | Gitarre | gitarre.jpg

Kann mir jemand bei der SQL-Abfrage helfen?
Meine momentane Abfrage:

SELECT * FROM Texte, Media WHERE Texte.Nr=Media.Nr AND Texte.Nr=1

--> ich möchte aus der Tabelle "Texte" den Inhalt aus Nr1 haben und aus "Media" die Bilder unter der Nr 1 (Also Inhalt aus Texte, der Nr1 und die 2 Bilder Trompete und Geige
--> Der richtige Text und die richtigen Bilder werden ausgegeben, allerdings wird der Text mehrfach ausgegeben (weil der Text aus Nr1, jedem Bild Nr1 zugewiesen wird) :/

Warum? Bzw, wie muss ich die SQL-Anfrage so filtern, dass mir der Text nur 1 mal ausgegeben wird`? Oder wie muss ich meine Tabellen verbessern?
Bin für eine Antwort sehr dankbar!!

 

[Hony%]

Hi.

Was du da machst ist ein Join. Daher, und das hast du auch richtig erkannt, wird das Tupel "Nr. 1" aus Texte mit jedem Tupel aus Media mit ebenfalls der "Nr. 1" verbunden.

Deutlicher sieht dein Query also so aus:

SELECT *
FROM Texte
INNER JOIN Media
ON Texte.Nr=Media.Nr
WHERE Texte.Nr=1

Wenn du also beide Informationen getrennt haben willst musst du auch zwei getrennte Abfragen nutzen.

 

[akretschmer]

H

Meine Tabellen mit Beispielinhalten (stimmt das von der Logik so? Kann man die beiden Tabellen überhaupt verbinden? ):

1. Tabelle "Texte"
Nr | Thema | Inhalt
1 | Instrumente | Hier steht ein Text drinnen...
2 | Anleitung | Hier steht ein TExt....

2. Tabelle "Media"
Nr| Beschreibung | Verweis
1 | Trompete | trompete.jpg
1 | Geige | geige.jpg
2 | Fidel |Fidel.jpg
3 | Gitarre | gitarre.jpg

Offenbar soll media.nr ein Fremdschlüssel auf texte.nr sein, oder? Dann stimmen aber schon mal die Daten nicht, weil in texte es keine nr=3 gibt.

PS.: so richtig klar wird nicht, warum Geige in Media nr=1 hat und Fidel die 2, also warum geige.jpg Instrument und die Fidel eine Anleitung ist.

Kann mir jemand bei der SQL-Abfrage helfen?
Meine momentane Abfrage:

SELECT * FROM Texte, Media WHERE Texte.Nr=Media.Nr AND Texte.Nr=1

Gewöhn Dir expliziete JOIN-Syntax an und 'select *' ab.

--> ich möchte aus der Tabelle "Texte" den Inhalt aus Nr1 haben und aus "Media" die Bilder unter der Nr 1 (Also Inhalt aus Texte, der Nr1 und die 2 Bilder Trompete und Geige
--> Der richtige Text und die richtigen Bilder werden ausgegeben, allerdings wird der Text mehrfach ausgegeben (weil der Text aus Nr1, jedem Bild Nr1 zugewiesen wird) :/

Warum? Bzw, wie muss ich die SQL-Anfrage so filtern, dass mir der Text nur 1 mal ausgegeben wird`? Oder wie muss ich meine Tabellen verbessern?
Bin für eine Antwort sehr dankbar!!

Du willst das offenbar aggregiert haben. Aggregatfunktionen fassen Ergebnisse zusammen , verdichten diese.

test=*# \d texte
  Table "public.texte"
 Column |  Type  | Modifiers
--------+---------+-----------
 nr  | integer | not null
 thema  | text  |
 inhalt | text  |
Indexes:
  "texte_pkey" PRIMARY KEY, btree (nr)
Referenced by:
  TABLE "media" CONSTRAINT "media_nr_fkey" FOREIGN KEY (nr) REFERENCES texte(nr)

test=*# \d media
  Table "public.media"
  Column  |  Type  | Modifiers
--------------+---------+-----------
 nr  | integer |
 beschreibung | text  |
 verweis  | text  |
Foreign-key constraints:
  "media_nr_fkey" FOREIGN KEY (nr) REFERENCES texte(nr)

test=*# select * from texte;
 nr |  thema  |  inhalt
----+-------------+--------------------------
  1 | instrumente | hier steht ein text
  2 | anleitung  | hier steht auch ein text
(2 rows)

Time: 0,187 ms
test=*# select * from media;
 nr | beschreibung |  verweis
----+--------------+--------------
  1 | trompete  | trompete.jpg
  1 | geige  | geige.jpg
  2 | fidel  | fidel.jpg
(3 rows)

Du machst bisher also faktisch sowas:

test=*# select texte.thema, texte.inhalt, media.verweis from texte left join media on texte.nr=media.nr;  thema  |  inhalt  |  verweis
-------------+--------------------------+--------------
 instrumente | hier steht ein text  | trompete.jpg
 instrumente | hier steht ein text  | geige.jpg
 anleitung  | hier steht auch ein text | fidel.jpg
(3 rows)

Willst aber

test=*# select texte.thema, texte.inhalt, array_agg(media.verweis) from texte left join media on texte.nr=media.nr group by texte.thema, texte.inhalt;
  thema  |  inhalt  |  array_agg
-------------+--------------------------+--------------------------
 anleitung  | hier steht auch ein text | {fidel.jpg}
 instrumente | hier steht ein text  | {trompete.jpg,geige.jpg}
(2 rows)

bzw.

test=*# select texte.thema, texte.inhalt, array_agg(media.verweis) from texte left join media on texte.nr=media.nr where texte.nr = 1 group by texte.thema, texte.inhalt ;
  thema  |  inhalt  |  array_agg
-------------+---------------------+--------------------------
 instrumente | hier steht ein text | {trompete.jpg,geige.jpg}
(1 row)

An der Stelle gleich eine Warnung: ich verwende PostgreSQL, das array_agg() gibt es in MySQL nicht. Die entsprechende group_concat - Funktion in MySQL zu finden überlasse ich Dir zur Übung.

Pluspunkte bekommst von mir, wenn Du auch auf PostgreSQL umsteigst. MySQL sollte wegen der Nebenwirkungen eh an Schulen verboten werden ...

 

[akretschmer]

Hallo, ich brauche Hilfe

Im übrigen: falsches Forum, Du verwendest MySQL. Vielleicht kann @Walter das noch grade biegen.

 

[Hony%]

test=*# select texte.thema, texte.inhalt, array_agg(media.verweis) from texte left join media on texte.nr=media.nr group by texte.thema, texte.inhalt;
  thema  |  inhalt  |  array_agg
-------------+--------------------------+--------------------------
anleitung  | hier steht auch ein text | {fidel.jpg}
instrumente | hier steht ein text  | {trompete.jpg,geige.jpg}
(2 rows)

Auch eine interessante Lösung. Du unterschlägst dabei allerdings die Beschreibung der Bilder. Wenn Barrierefreiheit eine Rolle spielt wird diese aber benötigt. Außerdem bleibt die Frage was für ein Datentyp in der Anwendung raus kommt. Ein String/Pseudo-Array ist deutlich unhandlicher als 2 getrennte Abfragen. Die funktionieren dann auch in MySQL.

Pluspunkte bekommst von mir, wenn Du auch auf PostgreSQL umsteigst. MySQL sollte wegen der Nebenwirkungen eh an Schulen verboten werden ...

Nebenwirkungen? Wenn du einen Realvergleich willst wäre ein Verbot durch das Betäubungsmittelgesetz der bessere Ansatz...

 

[akretschmer]

Auch eine interessante Lösung. Du unterschlägst dabei allerdings die Beschreibung der Bilder. Wenn Barrierefreiheit eine Rolle spielt wird diese aber benötigt. Außerdem bleibt die Frage was für ein Datentyp in der Anwendung raus kommt. Ein String/Pseudo-Array ist deutlich unhandlicher als 2 getrennte Abfragen. Die funktionieren dann auch in MySQL.

Da kann geholfen werden:

test=*# select texte.thema, texte.inhalt, array_agg(media.verweis), array_to_string(array_agg(media.beschreibung),', ') from texte left join media on texte.nr=media.nr where texte.nr = 1 group by texte.thema, texte.inhalt ;
  thema  |  inhalt  |  array_agg  | array_to_string
-------------+---------------------+--------------------------+-----------------
 instrumente | hier steht ein text | {trompete.jpg,geige.jpg} | trompete, geige
(1 row)

Die Beschreibung nun als kommaseparierte Liste. Also mit Beschreibung und ein String.

Er kann natürlich auch die Aggregation weglassen und dann schauen, wie viele Rows zurückkommen. Aber genau das wollte er ja vermeiden.

 

[Hony%]

Er kann natürlich auch die Aggregation weglassen und dann schauen, wie viele Rows zurückkommen. Aber genau das wollte er ja vermeiden.

Genau das hab ich anders verstanden.

Ich vermute er erwartet ein Ergebnis in dieser Form:
1 | Instrumente | Hier steht ein Text drinnen...
1 | Trompete | trompete.jpg
1 | Geige | geige.jpg

Theoretisch wäre das über UNION lösbar. Aber sobald sich eine Tabelle ändert funktioniert das dann auch nicht mehr.

 

[akretschmer]

Genau das hab ich anders verstanden.

Vielleicht verrät er uns ja noch ...