[SOLVED] Rank by Views with MySQL

Rank by Views with MySQL

I love MySQL and I have been using it during the last 2 years. But now I'm stuck...

I have three tables

 Files
---------
id | name  | views | folder_id
1  | car   | 274   |   1
2  | bike  | 100   |   1
3  | train | 120   |   2

 Folders
---------
id | name    | storage_id
1  | folder1 |     1
2  | folder2 |     2

 Storage
---------
id | name
1  | storage1
2  | storage2
3  | storage3

I would like to make a mysql query, so that the Storage's table is ordered by the total amount of views of all the files inside each folder which is inside a storage.

My first approach is to add the total views of each file grouping by the folder_id but then, how can I pass that value to the folders' table to then order the storage's table?

I'm sorry for my poor explanation. I'm still a noob.

The idea is to order de storage's table by the total amount of views of the files

Thanks in advance

Solution

Join the tables, group by Storage and sort by the total number of file views:

SELECT      Storage.*
FROM        Storage
  LEFT JOIN Folders ON Folders.storage_id = Storage.id
  LEFT JOIN Files   ON Files.folder_id    = Folders.id
GROUP BY    Storage.id
ORDER BY    SUM(Files.views) DESC

See it on sqlfiddle.