[mysql] Tabellen richtig verknüpfen

[BadByte]

hey leuts.

ich versuche mich gerade an einem Forum....
Ich möchte so, wie in dem ComputerbaseForm bei einem Thema, den Autor des
letzten Beitrages (Datum ect..) anzeigen.
Alle Themen befinden sich in einer Tabelle, und die Beiträge befinden sich in einer
anderen.
Um die Beiträge den Themen zuzuordnen giebt es in der BeiträgeTabelle eine Spalte
mit der Id des Themas. Es haben also alle Beiträge die zu einem Thema gehören, in
dieser Spalte den gleichen Wert.
So, jetzt möchte ich diese Tabellen so verknüpfen, dass immer nur der Datensatz in
der BeiträgeTabelle mitausgeben wird, der der aktuelle ist...

ich hab mir das so vorgestellt:

SELECT t.autor, t.datum, t.title, t.hits, a.autor, a.datum
FROM forumthemen t
LEFT  JOIN forumantworten a ON t.id = a.themenid

jetzt kommen aber leider die themen immer so oft zurück, wie's beiträge dazu giebt,
was muss ich noch einfügen, damit die Duplikate rausfliegen, und nur der Datensatz bleibt, mit den aktuellen Beitragsdaten?

 

[KonKorT]

Ohne jetzt länger den Code angeschaut zu haben, suchst Du vielleicht einfach das hier ?!

SELECT t.autor, t.datum, t.title, t.hits, a.autor, a.datum
FROM forumthemen t
LEFT  JOIN forumantworten a ON t.id = a.themenid ORDER BY a.datum DESC LIMIT 0, 1

Sofern Du mehrere Themen ausgeben willst, und nicht jetzt nur das des aktuellsten Themas empfiehlt sich:

SELECT t.autor, t.datum, t.title, t.hits, a.autor, a.datum
FROM forumthemen t
LEFT  JOIN forumantworten a ON t.id = a.themenid GROUP BY a.datum ORDER BY a.datum DESC

 

[BadByte]

ok, deine Lösung hat noch einen Fehler,
ich möchte nicht die mit dem gleichem Datum zusammenfassen, sondern die die zum gleichem Thema gehören. Mann muss also GROUP BY auf die Spalte themenid anwende,
also muss es so heißen:

SELECT t.autor, t.datum, t.title, t.hits, a.autor, a.datum
FROM forumthemen t
LEFT  JOIN forumantworten a ON t.id = a.themenid GROUP BY a.themenid ORDER BY a.datum DESC

trozdem danke :-)

 

[BadByte]

k, funktioniert doch noch nicht so wie es soll, manchmal giebt es ja (noch) keine Antworten auf ein Thema, also ist in der forumantworten Tabelle keine Eintrag...
Es wird auch immer nur EIN Thema zurückgegeben auf das noch keine Antworten hat...
Was muss ich einbauen, damit alle zurückgegeben, werde (am besten noch nach Datum sortiert)?

 

[davidbaumann]

Hat ne Weile gedauert, aber ich hab nen Vorschlag:

select themen.titel, 
(select top 1 beitrag.benutzer 
from beitrag
where beitrag.themaid = themen.id
order by beitrag.datum desc) as benutzer,
(select top 1 beitrag.datum 
from beitrag
where beitrag.themaid = themen.id
order by beitrag.datum desc) as datum
from themen

 

[riod]

versuch statt "join on" ein "where t.id = a.id"

 

[BadByte]

@ davidbaumann
könntest du mir deine Lösung bitte erklären, ich versteh z.B. nicht was das "select top 1" in der 2. Zeile heist.
Ich kann es desswegen auch net anpassen....

@ riod:
funktioniert leider net, du meinst doch das es dann so heißen soll:

SELECT t.autor, t.datum, t.title, t.hits, a.autor, a.datum
FROM forumthemen t
LEFT WHERE t.id = a.themenid GROUP BY a.themenid ORDER BY a.datum DESC

oder?

 

[BadByte]

also eine Lösung hätt ich noch, is zwar greislig...aber vorrübergehend:
Ich erstelle jedesmall wenn einer ein Thema erstellt, gleich auch eine Antwort, die aber nicht angezeigt wird, und nur dazu da ist, anzuzeigen wann der letzte beitrag gemacht wurde....

 

[Benz0l]

Hmm... warum so kompliziert?

SELECT t.autor, t.datum, t.title, t.hits, a.autor, a.datum FROM forumantworten WHERE a.themenid = THEMENID ORDER BY t.id DESC LIMIT 0,1

Ändere deine Spaltennamen nochmal ab... auf jeden Fall spuckt er dir dann den jeweils letzten Beitrag eines Themas aus. Da du das ganze bestimmt in einer Schleife hast, kannst du das so für jedes Thema abfragen.

 

[BadByte]

@ Benzol:
geht nicht, Fehlermeldung:

" #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY a.datum ASC ON t.id = a.themenid GROUP BY a.themenid
LIMIT 0, 30' at line 1"

wenn man des "ORDER BY a.datum ASC" rausmacht, dann gehts, aber dann kommt immer der 1. Beitrag, ich will aber den letzten

 

[DerEineDa]

Ohne mir jetzt genauere Gedanken darüber gemacht zu haben... Wenn du den letzten und nicht den ersten Datensatz haben möchtest, dann benutze "ORDER BY a.datum DESC". Außerdem muss das ORDER BY hinter dem GROUP BY stehen, und LIMIT ganz am Ende.

Wie davidbaumann vorschlug, könnte man das auch mit einer Unterabfrage lösen, wobei ich glaube, dass du auch so zum Ziel kommst, wenn du absteigend nach Datum sortierst und die Reihenfolge der Befehle einhältst:
GROUP BY (weiß ich nicht, ob du das wirklich brauchst, denn du willst ja nur die erste Zeile)
HAVING (brauchst du wohl nicht)
ORDER BY
LIMIT


Nachtrag:
Okay, nun habe ich mir Gedanken darüber gemacht und werde dir sagen, dass es nur mit übel vielen Unterabfragen möglich ist. Ich habe hier gerade auf meinem eigenen Forum eine solche Abfrage gemacht, und das ist wirklich extrem schlechter Stil - die Abfrage werde ich dir auch vorenthalten

Aber ich kann dir sagen wie du es besser machst: Du machst erstmal eine ganz banale Abfrage, welche Threads du überhaupt anzeigen willst, also einfach nur eine Abfrage auf die Thread-Tabelle. Ich gehe mal einfach davon aus, dass du PHP benutzt. Du hast dann also die Ergebnisse samt Thread-ID in einem Array, das du natürlich durchläufst, um die Topic-Liste zu erzeugen. Genau das ist der Zeitpunkt, wo du dann für jede einzelne ThreadID in deinem Array eine weitere Abfrage auf die Beiträge-Tabelle machst, wo du dann mit "... WHERE a.id = $topicID ORDER BY a.datum DESC LIMIT 1" den neusten Beitrag für den entsprechenden Thread bekommst.

Wenn du jedoch darauf bestehst, dass du eine einzige Abfrage machst, wo jeder Thread samt letzter Antwort in einer Tabelle steht, dann kommst du kaum drum herum, dass du eine Unterabfrage benutzt, um eine neue Tabelle zu generieren, in der alle Thread-Beitrags-Kombinationen enthalten sind (also jeder Thread auch mehrfach) - soweit warst du am Anfang schon, und das war schlecht. Also quasi deine Ur-Abfrage als neue Tabelle:

SELECT * FROM (
   SELECT t.autor AS tAutor, t.datum AS tDatum, t.title, t.hits, a.autor AS aAutor, a.datum AS aDatum
   FROM forumthemen t
   LEFT JOIN forumantworten a ON t.id = a.themenid
) AS alles

Anstatt das * benutzt du dann verschiedene Aggregatfunktionen wie MAX(), um dann eben nur das letzte Ergebnis zu bekommen und dann eben auch wieder mit GROUP BY. Wahrscheinlich brauchst du dann für die SELECT-Klausel wiederum Unterabfragen.

Wie gesagt, ich hab das hier gerade geschafft, aber das war so verschachtelt und hässlich, dass ich mir sicher bin, dass mein Vorschlag schöner und effizienter ist.

 

[ag3nt]

Also erstmal eine kleine Frage zum "Fred-Ersteller" willst du dir die Betragsdetails generell zu allen(!) Topics anzeigen lassen oder bezieht sich das nur auf ein(!) Topic?

Im ersteren Falle würde folgendes leifern was du brauchst (einmal vorausgesetzt die "Datums-Spalte" ist eindeutig, wenn nicht kannst die folgende Abfrage nicht verwenden[/]):

--Part 1: Alle Threads mit Antworten
SELECT 	t.autor, t.datum, t.title, t.hits, a.autor, a.datum
FROM 	forumthemen AS t
	INNER JOIN 
		forumantworten AS a 
	ON 	t.id = a.themenid		
WHERE	a.datum = (
		SELECT	MAX( a.datum )
		FROM	forumantworten AS nested
		WHERE	a.themenid = nested.themenid
	)
	
--Part 2: Alle Threads OHNE Antworten
UNION ALL

SELECT 	t.autor, t.datum, t.title, t.hits, NULL, NULL
FROM 	forumthemen AS t		
WHERE	NOT EXISTS (
		SELECT	NULL
		FROM	forumantworten AS nested
		WHERE	t.themenid = nested.themenid
	)

Anmerkung: Ein Inner-Join ist definitv die bessere Wahl, da erheblich schneller als ein Outer-Join, und die Situtation, dass ein Topic existiert bei dem keine Beiträge vorhanden sind halte ich für sehr unwahrscheinlich.
Nebenbei solltest du dein Datenbankmodell noch einmal überdenken, den ersten Beitrag getrennt von den Antworten zu speichern ist weder effizient noch vereinfacht es das ganze Vorgehen.

 

[BadByte]

kann es sein, dass in MYSQL 4.0.18 das Abfragen von einer Abfrage also das hier:

SELECT * FROM
(SELECT * FROM tabelle)

noch nicht vorhanden ist?
weil auf meiner Testumgebung, wo ich eine MySQL 5 hab funktioniert das, auf meinem Webserver, wo ich leider nur des 4er hab kommt immer eine MySQL Fehlermeldung:

"#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select * from forumthemen ) LIMIT 0, 30' at line 1 "

 

[DerEineDa]

Ich bin mir sicher, dass MySQL das quasi schon immer beherrschte. Unterabfragen sind ja nun auch nichts so exotisches, auch wenn Anfänger das gerne glauben.

Die für die FROM-Klausel erstellte Tabelle muss einen Namen haben (ich glaube der fehlt bei dir, aber zeige am besten deine ganze Abfrage) und es dürfen keine zwei Spalten den selben Namen tragen. Für letztes habe ich in meinem Beispiel gesorgt, indem ich die doppelten Spalten mit AS umbenannt habe.

Es muss also folgendermaßen aussehen:

SELECT * FROM (SELECT * FROM lala JOIN lolo ON la = lo) AS neuTabelle

 

[BadByte]

meine derzeitige Abfrage schaut so aus:

 SELECT * FROM (
SELECT t.id AS tid, t.autor AS tautor, t.datum AS tdatum, t.title, t.hits, a.autor AS aautor, a.datum AS adatum, a.id AS aid
FROM forumthemen t
LEFT JOIN forumantworten a ON t.id = a.themenid
) AS alles
GROUP BY tid
ORDER BY adatum DESC

NACHTRAG:
die MySQl Fehlermeldung lautet immer:
#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT t . id AS tid , t . autor AS tautor , t . datum AS tdatu

 

[DerEineDa]

Hmm, Mist, ich hab mich geirrt: http://dev.mysql.com/doc/refman/5.1/de/rewriting-subqueries.html

Wie's aussieht kann MySQL < 4.1 tatsächlich noch keine Unterabfragen. Das ist ja ärgerlich.

 

[BadByte]

zefix, was mach ich denn dann?
mit PHP umständlich zwei Abfragen machen, und die dann sortieren?, bleibt mir wohl nix anderes übrig ;(

 

[TheNoxier]

Aus Performance-Gründen würde ich statistische Daten gleich mit in die "forumthemen" einbauen. Spart Query/Verarbeitungszeit und ist einfacher zu verwalten

Gruß
nox

 

[BadByte]

also du meinst ich mach bei forumthemen noch die entsprechenden Spalten rein, die mich aus forumantworten interresieren.
Die muss ich dann halt bei jedem neueintrag updaten....

 

[TheNoxier]

Genau, denn du musst es nur einmal bei jedem neueintrag update.
Du musst bedenken, das die Forenansicht wesentlich öfters angeklickt wird, als ein post hinzugefügt wird. Beim Eintragen hast halt 0,2sec länger laufenden Script, aber beim Auslesen sparst du die nen den gesamten JOIN bzw in deinem Fall sogar 2 komplette Abfrage. Das könnte glatt 50% schneller sein, wenn nicht mehr.
Kleinvieh macht auch mist, vor allem wenn es 1000x aufgerufen wird

Wenn deine 2 Abfragen mit Join vorher 2sec gedauert hätten und nachher ohne join und ohne 2 Abfragen nur noch 0,5 Sekunden, würden 1000 Seite Aufrufe anstatt 2000 Sekunden (33 Minute) nur noch 500 Sekunden (8 Minuten dauern).

Ja ich weiß sind fiktive Zahlen, aber du solltest Daten die du 1000x abrufst (Themen Aufruf) eher optimieren, als Sachen die nur einmal passieren. Und ob der User 0,3 Sekunden oder 0,8 Sekunden wartet, weil noch die Statistiken aktualisiert werden, macht auch keinen Unterschied, aber ob dein Server 8 Minuten oder 33 Minuten unter Last ist, schon

Noch eine Stufe "besser" wären rein statische Seiten.
Sprich wenn du einen Post machst, wird die Forenansicht, Themenansicht, Thema + Unterseiten auf HTML gebannt.
Macht dann aber erst Sinn wenn du wesentlich mehr Lese- als Schreibzugriffe hast.
z.B. 10000 User lesen pro Tag das Forum, aber nur 50 Beiträge werden geschrieben.
Dann würdest du Performance gewinnen - Oder du stellst nen schnelleren Server hin

Gruß nox