Abfrage aus 3 Tabellen - Suchbedingung?

[preko]

Hi,

ich habe 3 (MySQL) Tabellen, die ich in einer Query abfrage.
Die 1. Tabelle heisst: kurse
Die 2. Tabelle heisst: anbieter
Die 3. Tabelle heisst: kursort
Soweit, so gut.

Das bisherige Abfrage sieht vereinfacht wie folgt aus:

...
SELECT
          kurse.kursstart AS kursstart,
          kurse.status AS status,
          kurse.traeger_id,
          anbieter.traeger_id,
          anbieter:kursort_id,
          kursort.traeger_id,
          kursort.kursort_id,
          kursort.plz AS plz

FROM 
          `kurse`
INNER JOIN
          `anbieter` ON kurse.traeger_id = anbieter.traeger_id
INNER JOIN
          `kursort` ON kurse.traeger_id = kursort.traeger_id
WHERE
          `status` = '1'
ORDER BY
          `kursstart` DESC
...

Ich habe in den Tabellen die Spaltenüberschrift "trager_id", welche als "Verknüpfungsspalte" definiert ist.

Nun ist es so, dass ein Anbieter mehrere Kursorte anlegen kann. Daher ist in den Tabellen "anbieter" und "kursort" eine Spalte "kursort_id".
Ich möchte, daß in der Darstellung der Seite der passende Kursort zu dem jeweiligen Kurs ausgegeben wird.

D. h., daß z. B.
Kurs 1: Anbieter A den Kurs 1 unter der Adresse A anbietet,
Kurs 2: Anbieter A den Kurs 2 unter der Adresse C anbietet,
Kurs 3: Anbieter A den Kurs 3 unter der Adresse B anbietet, usw..

Wie muß der Suchalgorhythmus ergänzt werden um das gewünschte Ergebnis zu erreichen? Hat jemand einen inspirierenden Vorschlag?


Beste Grüße,
preko

 

[olqs]

So wie ich das überblicke ist das in deinem Datenbank Layout nicht möglich, da eine Verknüpfung zwischen dem Ort und dem Kurs fehlt.
Die Verknüpfung über die traeger_id verbindet nur den Kurs mit dem Anbieter und die Orte mit dem Anbieter. Die Kurse sind aber nicht mit dem Ort verknüpft.

Um dein Beispiel aufzugreifen, bekommst du nur solche Ausgaben:
Kurs1: Anbieter A den Kurs1 unter Adresse A, B, C
Kurs2: Anbieter A den Kurs2 unter Adresse A, B, C
...

Gruss
olqs

 

[preko]

@ olgs:

genau das ist ja mein Problem. Er kombiniert die Daten tatsächlich so, wie Du es formuliert hast, sprich:

Kurs1: Anbieter A den Kurs1 unter Adresse A, B, C
Kurs2: Anbieter A den Kurs2 unter Adresse A, B, C
...

Heißte das also, daß die einzige Lösung die Änderung des Datenbanklayouts ist?


Beste Grüße,
preko

 

[Chefkoch]

Wie sieht denn deine Tabellenstruktur aus? Welche Normalform hast schon erreicht?

 

[preko]

@ Chefkoch:

der Aufbau meiner Tabellen sieht im Prinzip wie folgt aus:

Tabelle 1 [kurse]:

| id* | traeger_id** | kursort_nr*** | kursname | kursanfang | kursende |
---------------------------------------------------------------------------------------------------

Tabelle 2 [anbieter]:

| id* | traeger_id** | name | strasse | ort |
--------------------------------------------------------


Tabelle 3 [kursort]:

| id* | traeger_id** | kursort_nr*** | plz | ort |
------------------------------------------------------------

* auto_increment
** Primärschlüssel
*** index


Beste Grüße,
preko

 

[Chefkoch]

Das ist ein katastrophaler pfusch, aber ich glaube es macht das, was du haben möchtest:

So würde ich mir das ER Modell vorstellen: siehe Anhang bild

Jeder Anbieter bietet 1..N Kurse an. Jeder Kurs wird von genau 1 Anbieter angeboten.

Jeder Kurs wird genau in einem Ort abgehalten. In jedem Ort werden 1..N Kurse abgehalten.

database x
An(bieter) (id*, name)
Kurse(id*,name, Fremdschlüssel Anbieter.id aid, Fremdschlüssel Ort.Id oid)
Ort(id*,name)


select x.an.name,x.ort.name,x.kurse.name from x.an,x.ort,x.kurse where x.an.id=x.kurse.aid AND x.ort.id=x.kurse.Oid

Anbietername Ortname Kursname
-------------------- ---------- --------------------------------------------------
Sun Hamburg Mathe
IBM Hamburg Dbanken
Micro salzburg Java

 

[preko]

@ Chefkoch,

zunächst einmal vielen Dank für Deinen Vorschlag. Dazu hätte ich mal eine Frage:

wenn ich Dich richtig verstanden habe, ist Deinen Worten nach Dein eigener Vorschlag katastrophaler Pfusch?
Wenn ja, bitte ich um paar kurze Worte der Erklärung, damit ich verstehe, wo das Problem liegt und ggf. eine geeignetere Lösung finden kann, denn ich möchte mich nicht in eine Notlösung verrennen, die mir bei späterer Arbeit Fallstricke vor die Füße wirft und zu Flickschusterei zwingt.​

Beste Grüße,
preko

 

[Biber2]

Moin preko,

ich sehe das nicht so pessimistisch.

Wenn Deine gepostete Strukturskizze stimmt, dann sollte es reichen, den zweiten "INNER JOIN" auf "zu "trager_id UND Kurs_nr" zu erweitern.

Allerdings kann unmöglich der angegebene PK der "Kursort"-Tabelle richtig sein.

| id* | traeger_id** | kursort_nr*** | plz | ort |
------------------------------------------------------------
* auto_increment
** Primärschlüssel
*** index

Das ist entweder ein Tippfehler oder Bullshit.

Tabelle 3 [kursort]:sollte als PK die kursort_nr haben, die zusätzliche AutoincrementID ist überflüssig und traeger_id macht nur Sinn, wenn die auch als FK definiert und abgesichert ist.

Meine Empfehlung:
Schmeiß die (meisten) Autoincrement-IDs weg und nimm als PKs
Tabelle 2 'anbieter' : die traeger_id
Tabelle 3.'Kursort": kursort_nr ; FK traeger_id.
-oder alternativ-
Nimm in Tabelle 3 tatsächlich diese Auto-ID als PK und bau dieses Feld als FK mit in die 'kurse' ein.
Was allerdings den Nachteil hat, dass dann immer ein Kursort-Datensatz vorhanden sein müsste, bevor sich ein Kurs anlegen lässt...

----
Erweiterung des zweiten INNER JOINs oben auf:

....

INNER JOIN 
          `anbieter` ON kurse.traeger_id = anbieter.traeger_id 
INNER JOIN 
          `kursort` ON kurse.traeger_id = kursort.traeger_id and
          kursort_nr = kursort.kursort_nr

Wozu hast Du denn den vielen künstlichen IDs eingeführt, wenn die nirgends als FKs oder zur Tabellen-VerJOINung benutzt werden?

Grüße
Biber

 

[Chefkoch]

Das ist meine erste Abfrage überhaupt mit 3 Tabellen

Die ganze Abfrage basiert darauf, dass ich den PK vom Anbieter und PK vom Ort als Foreign Keys in die Tabelle Kurse reinstelle. Dann sage ich einfach vergleiche PKwert aus Anbieter mit FKwert in Kurse und genau das gleiche dann auch mit PK vom Ort.

ein anderes problem ist dann halt die referentielle Integrität. Sind ja 2FK in der Tabelle kurse.


Ich habe es jetzt nochmals mit Anbietern und Städten getestet und es funktioniert.

Anbieter
1 Sun
2 IBM
3 Micro

Ort
1 Paris
2 Hamburg
3 Salzburg

Kurse
id Name FK FK
1 Mathe 1 2 (1=Sun, 2= Hamburg)
2 DB 2 2 (2=IBM, 2= Hamburg)
3 Java 33 (3=Micro, 3=Salzburg)


Anbietername Ortname Kursname
-------------------- ---------- --------------------------------------------------
Sun Hamburg Mathe
IBM Hamburg Dbanken
Micro salzburg Java

 

[olqs]

Das ist sicher ein Lösungsansatz und als solch einen Pfusch find ichs nicht mal.

Die Tabelle Kurse bildet in dem Layout eine m:n Beziehung zwischen den Anbietern und Orten ab und an dieser Verknüpfung hängt halt noch ein Attribut mit dem Kursnamen.

Problematisch wirds erst wenn man gleiche Kurse hat, die immer wieder mal abgehalten werden, da kommts nämlich schnell zu unnötigen Redundanzen.

Ich würd dazu das oben von Chefkoch vorgeschlagene Layout ein bisschen erweitern:

Anbieter
pk_anbieter  name
1            Sun
2            IBM
3            Micro

Ort
pk_ort  name
1       Paris
2       Hamburg
3       Salzburg

Kurse
pk_kurs name   beschreibung   kursdauer
1       Mathe  Einsteigerkurs 5
2       DB     Oracle         3
3       Java   Servlets       4

Verknuepfung
pk_verknuepfung fk_anbieter fk_ort fk_kurs datum    startzeit
1               1           2      1       22.01.08 09:00
2               2           2      2       07.02.08 11:00
3               3           3      3       07.02.08 10:00
4               2           1      2       11.03.08 09:00

In der Tabelle Verknüpfung hab ich noch eine Zeile hinzugefügt. Der Anbieter "IBM" hält an dem zweiten Datum und in einer anderen Stadt nochmal den Datenbankkurs. Der Kursname, die Beschreibung und die Dauer sind aber jetzt nicht doppelt in der DB gespeichert.

Abfragen sind dann auch nicht so schwer. Alle Kurseinträge bekommst zu dann so:

SELECT *
FROM verknuepfung
JOIN anbieter on pk_anbieter = fk_anbieter
JOIN ort on pk_ort = fk_ort
JOIN kurse on pk_kurs = fk_kurs

Aufpassen muss man hier nur bei Felder die gleich benannt sind, aber die in unterschiedlichen Tabellen vorkommen. Hier z.B. name
Im SQL Statement müssen die mit Tabellenname angegeben werden, z.B. anbieter.name, oder ort.name

Desweiteren würde ich auch Contraints für die Foreign Keys festlegen, sonst kanns wie von Chefkoch auch schon angemerkt wurde schnell zu Integritätsproblemen kommen.
Falls zu Mysql nutzt muss dafür InnoDB als DB-Engine genutzt werden.

Gruss
olqs