(my)SQL-Query mit mehreren Tabellen

[Andreas]

Hallo!
Ich habe eine ein bisschen kompliziertere Abfrage per PHP in mySQL zu machen und hoffe, dass mir hier jemand helfen kann: In einem Picture-Voting-System werden je 20 Bilder, die zu jeweils einer von 10 Veranstaltungen gehören, von Besucher mit Werten von -1, 0 oder +1 bewertet. Jetzt möchte ich die Bilder in der Reihenfolge der Bewertungen angezeigt bekommen. Bilder, die bisher nicht bewertet wurden bekommen als Wertung eine 0, bei den anderen werden die Wertungen zusammengezählt (z.B.: ein Bild wurde 3 mal mit +1, 1 mal mit 0 und 1 mal mit -1 bewertet = +2).

Die Tabelle sind: veranstaltungen, fotos und votes wobei
veranstaltungen.id=fotos.vid
fotos.id=votes.fid
Die einzelnen Bewertungen sind je eine Zeile in votes und werden in der Spalte voting gespeichert.

Weiß jemand die passende Abfrage?

Vielen Dank,
Andreas

[Bene]

SELECT * FROM fotos
INNER JOIN votes ON fotos.id = votes.fid
ORDER BY votes.voting DESC

Das sollte dir die Fotos in der Wertungsreihenfolge ausgeben.
Höchste Wertung dabei ganz oben da "DESC" = "Absteigend"

Wenn du nur die Votos einer Veranstaltung haben willst musst noch auf deine Veranstaltungs Tabelle joinen.

SELECT * FROM fotos
INNER JOIN veranstaltungen ON fotos.id = veranstaltungen.id
INNER JOIN votes ON fotos.id = votes.fid
ORDER BY votes.voting DESC

Grüße,
Bene

[Andreas]

Hallo Bene,
vielen Dank für Deine Antwort!
Mit deiner Hilfe habe ich mir jetzt was zusammengebastelt, was glaube ich funktioniert:

select *, sum(votes.voting) as v from fotos
inner join veranstaltungen on fotos.vid=veranstaltungen.id
left outer join votes on fotos.id=votes.fid
group by fotos.id
order by v desc

Damit werden alle Bilder aller Veranstaltungen (auch nicht bewertete) angezeit. Die einzelnen Bewertungen der einzelnen Benutzer sind jeweils eine extra Zeile in der Tabelle (wegen IP-Sperre) deshalb brauche ich die Summe.

Das einzig unschöne ist noch, dass ich für unbewertete Bild "NULL" statt "0" zurückgegeben bekomme, aber das ist auch nicht so schlimm.

Viele Grüße,

Andreas

[Bene]

Hi Andreas,

um das "NULL" zu eliminieren könntest du alle unbewerteten fotos
mittels UNION hinzufügen.

ungefähr so:

select *, sum(votes.voting) as v from fotos
inner join veranstaltungen on fotos.vid=veranstaltungen.id
INNER join votes on fotos.id=votes.fid
group by fotos.id
order by v desc

UNION

select *, 0 as v from fotos
inner join veranstaltungen on fotos.vid=veranstaltungen.id
LEFT join votes on fotos.id=votes.fid
WHERE votes.voting = 'NULL'

---

Kann sein, dass du GROUP BY und ORDER BY ans Ende hängen musst.
Aber so sollte es gehen.

Grüße,
Bene

[Andreas]

Super Bene!

Bei meinem Statement hat die Sortierung nämlich auch nicht wirklich geklappt: Die unbewerteten Bilder kamen immer am Schluss.
So geht's jetzt:

select *, sum(votes.voting) as v from fotos
INNER join votes on fotos.id=votes.fid
group by fotos.id
UNION
select *, 0 as v from fotos
LEFT join votes on fotos.id=votes.fid
where votes.voting IS NULL
group by fotos.id
order by v desc

Vielen Dank nochmal,

Andreas

[Ilja]

yo,

je öfter ich solche nicht funktionierenden abfragen sehe, wo viele glauben, dass es richtig ist und wohl nicht mehr wiederkommen werden, könnte ich mysql für diese blöde errungenschaft mit GROUP BY erschiessen.

falls du trotz dem glauben, eine funktionierende abfrage zu besitzen, nochmals hier ins forum schaust, dann wundere dich nicht, dass deine abfrage merkwürdige daten ausschmeißt. mysql verleitet eine grundregel zu verletzten, indem es keine fehlermeldung anzeigt, wenn spalten ausgegeben werden, über die nicht gruppiert wurde und die keine aggregat-funktionen sind. und genau das hast du mit dem * Symbol gemacht. Ausserdem ist der UNION unnötig, machen einen OUTER JOIN daraus.

deine abfrage müsste so lauten, die allerdings erst ab der Version 4.1+ funktioniert:

SELECT *,
   IFNULL((SELECT SUM(votes.voting) votes v2 WHERE v2.fid = v1.fid), 0) AS Rating
FROM fotos f
LEFT JOIN votes v1 ON f.id = v1.fid
ORDER BY Rating DESC

[Ilja]

yo,

lass den LEFT JOIN mal weg, sonst werden es zuviele Werte.

SELECT f.*, IFNULL((SELECT SUM(votes.voting) votes v2 WHERE v2.fid = f.id), 0) AS Rating
FROM fotos f
ORDER BY Rating DESC

Ilja