[SQL] Find 5 greatest totals after summing (quantity x value) for each qualifying user

I currently have a table which displays the inventory values of users. It works, but it displays all users and their inventory values in a random order. I'm wanting to limit the table to the top 5 descending inventory values only.

This data cuts across three tables:

1. Users
2. Inventories (contains a row for each item a user has in their inventory)
3. Items (contains the value of the items)

echo "<table>
<th>Inventory Value</th>
<th>Username</th>";

// Choose the users we want (verified users).
$refineUsers=$db->query("SELECT userID FROM users WHERE accountVerified = 'true'");
while($users=$db->fetch_row($refineUsers)) {
// Fetch the inventories of the users we want.
$fetchInventories=$db->query("SELECT * FROM inventories WHERE userID = '".$users['userID']."'");
$totalInventoryValue=0;
while($inventories=$db->fetch_row($fetchInventories)) {
// Fetch the values of the items.
$fetchItemsData=$db->query("SELECT value FROM items WHERE itemID = '".$inventories['itemID']."'");
while($items=$db->fetch_row($fetchItemsData)) {
// Calculate the values of the various items within the user's inventory.
$totalItemsValue=($items['value'] * $inventories['quantity']);
// Calculate the total value of the user's inventory.
$totalInventoryValue+=$totalItemsValue;
}
}
// Display the values of each user's inventory.
echo "<td>".money_format($totalInventoryValue)."</td>";
echo "<td>".username($users['userID'])."</td>";
echo "<tr>";
}

echo "</table>";


I'm guessing this could be done with a JOIN query, but I'm not very experienced and haven't done one that cuts across three tables before.

Any solutions would be much appreciated.

DB Fiddle

Continue reading...