I have three tables in a MySQL (Percona) server:
What I'm trying to do is find the top albums whose photos have won the most battles. For each album, I want to display a thumbnail of its featured photo, identified by album_photo
.photo
My query so far is:
SELECT
a.id,
a.name,
feature.thumb_path,
SUM(p.wins) wins,
SUM(p.battles) battles,
SUM(p.wins) / SUM(p.battles) album_popularity
FROM
photo p,
album a,
album_photo ap
LEFT JOIN photo feature ON ap.photo_id = feature.id AND ap.featured = 1
WHERE p.id = ap.photo_id AND ap.album_id = a.id
GROUP BY a.id, feature.thumb_path
ORDER BY album_popularity DESC, SUM(p.battles) DESC
This query is returning results in the order I'd expect, but some of the thumb_path
values are NULL. Every album has a featured photo. In other words, album
is one-to-many with album_photo
, and at least one of those rows in album_photo
has featured = 1
I'm no DBA, so this might be obvious for some of you but... why is this happening? I feel like I'm 85% of the way there, but I'm missing a JOIN
, or referencing an already referenced table by a new alias, or a subquery of some sort. Maybe I have to re-join both album_photo
and photo
again?
Any insight would be appreciated.
UPDATE
After adopting the syntax suggested by Rick James, I got this query:
SELECT
a.id,
a.name,
ap.photo_id,
p.thumb_path,
SUM(p.wins) wins,
SUM(p.battles) battles,
SUM(p.wins) / SUM(p.battles) album_popularity
FROM album a
JOIN album_photo ap ON a.id = ap.album_id
JOIN photo p ON ap.photo_id = p.id
GROUP BY a.id
HAVING SUM(p.battles) > 1
ORDER BY album_popularity DESC, SUM(p.battles) DESC
Which is populating all the fields, but is returning the first photo (lowest ID) in the album, not the featured photo. So I want to somehow link each row in the resultset to the row in album_photo
that matches each row's album_id
, then link again back to photo
. I tried doing just that with:
SELECT
a.id,
a.name,
apf.photo_id,
pf.thumb_path,
SUM(p.wins) wins,
SUM(p.battles) battles,
SUM(p.wins) / SUM(p.battles) album_popularity
FROM album a
JOIN album_photo ap ON a.id = ap.album_id
JOIN photo p ON ap.photo_id = p.id
LEFT JOIN album_photo apf ON p.id = apf.photo_id AND apf.featured = 1
LEFT JOIN photo pf ON apf.photo_id = pf.id
GROUP BY a.id
HAVING SUM(p.battles) > 1
ORDER BY album_popularity DESC, SUM(p.battles) DESC
This is returning the correct rows, but the photo information is NULL
except for albums where the featured photo for a given album is also the photo with the lowest id
.