0

I have three tables in a MySQL (Percona) server:
enter image description here

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.

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.

2
  • When I get rid of the LEFT, then the resultset returned is entirely different. Fully populated, but it's returning a different set of rows. Jan 7 at 18:42
  • As I said: that means that either your join conditions are wrong or your data isn't the way you think it is. Rebuild the query, starting with just two tables and adding more in, one at a time, until the resultset starts to "go wrong", then figure out /why/ it went wrong at that point (dodgy data or join conditions), then "fix" that. Repeat this process until the query is back up to "full strength".
    – Phill W.
    Jan 10 at 8:35
1

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

4
  • When I get rid of the LEFT, then the resultset returned is entirely different. Fully populated, but it's returning a different set of rows. Jan 7 at 18:42
  • @Quasipickle - A different set of rows? Or are the rows in a different order? I a particular order is needed, you must add an ORDER BY clause.
    – Rick James
    Jan 7 at 23:35
  • A different set of rows. My query does have an ORDER BY clause. Thinking about it - I'm just going to redesign my DB a bit so album gets a featured_photo_id column, rather than recording that info in album_photo. Thanks for your attention nonetheless. Jan 8 at 4:39
  • 1
    @Quasipickle - Good. Previously you had many-to-many, which allowed each album to have many featured_photos. Now you have many-to-one, which is "logically" the right relationship.
    – Rick James
    Jan 8 at 18:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.