Why is field in JOIN query null?

Question

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.

Answer 1

If the database can't find a matching record in the photo table then there are only two possibilities:

• Either: the record really isn't there (but you say that there should be).
• Or: your Join conditions are wrong.

If you are saying that "Every album has a featured photo." (I'm guessing that they don't, BTW!) then you can lose the LEFT join and use a regular, INNER join.
Actually, I would suggest that you should always use "inner join" syntax in your queries - you're currently mixing implicit ("comma-join") and explicit join syntaxes and it's really difficult to see what's going on.

Answer 2

Use JOIN, not LEFT JOIN. LEFT is useful when the row in the second table may be missing, and you _want_ NULLs`.

Use the modern syntax:

FROM a
JOIN b ON b.x = a.y

That is, use ON for saying how tables are 'related'; use WHERE for 'filtering'.

See this for performance guidance on many-to-many table indexing: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table