MYSQL: Unterabfrage

[monk]

Hallo

Ich habe folgende zwei Tabellen:

daten
id	datum 			
6765	1271439813		
6764	1271439845		
6763	1271439868		

files
ins_id	filename		id	
6765	test.jpg               10
6765	test2.jpg              11
6765	test3.jpg              12

Ich möchte nun die beiden Tabellen verknüpfen und die neuesten 50 Datensätze anzeigen lassen, dabei soll jeweils von der Tabelle files der Filename mit der höchsten id ausgegeben werden.

Anscheinend geht das nur mit einer Unterabfrage. Habe schon vieles versucht, aber da kommt nichts gescheites raus. Z.B.:

SELECT i . * , p . * 
FROM daten i
LEFT JOIN files p ON i.id = ( 
SELECT p2.ins_id
FROM pics p2
WHERE p2.ins_id = i.id
ORDER BY p2.id DESC 
LIMIT 1 ) 
GROUP BY i.id
ORDER BY i.datum DESC 
LIMIT 50

Allerdings ist hier filename und ins_id immer gleich...

[Sascha Ahlers]

Nach Deiner Abfragestruktur existiert noch eine Tabelle Namens "pics", welche Du hier nicht aufgelistet hast, wie soll man Dir hier dann helfen können?


Gestützt auf Vermutungen und Deiner Beschreibung, hätte ich aber wohl so die Abfrage gestaltet, ohne Deine vorher zu lesen:

SELECT * FROM daten, files
WHERE daten.id = files.ins_id AND daten.id IN (
     SELECT daten.id FROM daten ORDER BY datum DESC LIMIT 0,50
) ORDER BY files.id DESC LIMIT 0,50;

OR

SELECT * FROM daten
LEFT JOIN files ON daten.id = files.ins_id
WHERE daten.id IN (
     SELECT daten.id FROM daten ORDER BY datum DESC LIMIT 0,50
) ORDER BY files.id DESC LIMIT 0,50;

[monk]

Entschulidge... pics = files

Leider bekomme ich mit beiden Abfragen die Fehlermeldung

#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Meine MySQL Version ist 4.1.20.

Entferne ich das LIMIT in der Unterabfrage gehts, aber dann stehen leider doppelte Werte im Ergebnis

[Sascha Ahlers]

Dann erkläre Dich doch bitte kurz, was hier für Dich doppelte Einträge heißt...

PS: Stimmt LIMIT darf ich ja auch nicht in Subqueries nutzen.

[monk]

Mit

SELECT * FROM daten
LEFT JOIN files ON daten.id = files.ins_id
WHERE daten.id IN (
     SELECT daten.id FROM daten ORDER BY datum DESC
) ORDER BY files.id DESC LIMIT 0,50;

werden die Datensätze so oft angezeigt, wie es Einträge hat mit gleicher id in der Tabelle files. Im Ergebnis stehen also drei Datensätze mit der id 6765 und den jeweiligen files (wenn man die Daten aus meinem 1. Beitrag nehmen würde).

Und ich möchte ja, dass je daten.id ein Datensatz mit einem zugehörenden bildnamen (files.filename) - nämlich der mit der höchsten id - ausgegeben wird.

[Sascha Ahlers]

Moment, es existiert aber keine id mit 6765, nur eine ins_id mit 6765. Und wenn das der Fremdschlüssel ist, ist es doch unweigerlich, dass der doppelt ist...

[monk]

doch, existiert... daten.id ist der Primärschlüssel, files.ins_id der Fremdschlüssel. Muss doch irgendwie möglich sein so eine Abfrage

[QUOTE=monk;508722]

daten
id	datum 			
6765	1271439813		
6764	1271439845		
6763	1271439868		

files
ins_id	filename		id	
6765	test.jpg               10
6765	test2.jpg              11
6765	test3.jpg              12

Edit: Damit wir uns recht verstehen: dass die id im selben Datensatz zweimal angezeigt wird ist natürlich klar! Ich meine aber, dass es mehrere Datensätze gibt die dann die id 6765 haben...

[monk]

So... nach ein paar Stunden glaube ich, ich habs:

SELECT *
FROM daten
LEFT JOIN files ON daten.id = files.ins_id
WHERE files.id = (

SELECT max( files.id )
FROM files
WHERE files.ins_id = daten.id
)
ORDER BY datum DESC
LIMIT 0 , 50;

...mist, doch nicht ganz. da werden mir nur die Datensätze angezeigt, zu denen auch ein Eintrag in der Tabelle files ist. Aber es kann eben sein, dass da keiner vorhanden ist

[nikosch]

dabei soll jeweils von der Tabelle files der Filename mit der höchsten id ausgegeben werden.

Eine ID ist kein sinnvolles Sortierkriterium.

[thomas_w]

...mist, doch nicht ganz. da werden mir nur die Datensätze angezeigt, zu denen auch ein Eintrag in der Tabelle files ist. Aber es kann eben sein, dass da keiner vorhanden ist

Dazu fällt mir folgendes ein...

SELECT daten.datum, MAX(files.ins_id)
  FROM daten
  LEFT JOIN files
    ON daten.id = files.id
GROUP BY daten.datum
ORDER BY daten.datum DESC
LIMIT 50;

Mir ist nicht ganz klar geworden, warum Du immer mit folgenden Spalten den JOIN zwischen den Tabellen aufbaust:

daten.id = files.ins_id

Spontan glaube ich, dass folgendes besser wäre, aber ich kann mich auch täuschen.

daten.id = files.id

Habe ich es nicht ausprobiert. Aber vielleicht stimmt die "Richtung"...

Grüße
Thomas

[Sascha Ahlers]

[...]

daten.id = files.ins_id

Spontan glaube ich, dass folgendes besser wäre, aber ich kann mich auch täuschen.
[...]

Ich hab wohl verstanden, wieso er das macht, denn das ist die Beziehung, files.ins_id scheint hier der Fremdschlüssel zu sein, files.id ist der Primärschlüssel. Und es macht keinen Sinn bei einer 1:n-Beziehung eine Primärschlüssel mit einen Primärschlüssel zu verbinden. Richtig konform wäre es natürlich nur, wenn es nicht files.ins_id heißt, sondern files.daten_id.