MySQL subquery on each record in group by

Question

This is the more advanced sequel to an earlier question.

Here's a link to an SQLFiddle to make things a bit clearer

I need to return something like (for each location):

Ground Floor
---------------
Garage
- Radiator
Kitchen
- Cooker
- Fridge

There's going to be even more levels than this but if I can get started I should hopefully be able to go further as needed.

Thanks

Answer 1

Since SQL cannot return nested records as you have formatted above, you need only to return a query which orders your rows by floor then by sub_location. You'll get both the location and sub_location repeated for each row, but in your application code loop when printing the results, you format them as you have above.

SELECT
  location.location_name,
  sublocation.sub_location_name,
  asset.asset_name
FROM
  location
  LEFT JOIN sub_location ON location.location_key = sublocation.location_key
  LEFT JOIN assets ON sub_location.sub_location_key = assets.sub_location_key
ORDER BY
  location.location_name,
  sub_location.sub_location_name

When looping over your rowset in your application, you then only print the new location or sub_location when it changes. Formatting is done in code.

Assuming your rows are all in the array $rowset:

// Store location, sub_location for each loop
$current_loc = "";
$current_subloca = "";
foreach ($rowset as $row) {
  // If the location changed, print it
  if ($row['location'] != $current_loc) {
    echo $row['location_name'] . "\n";
    // Store the new one
    $current_loc = $row['location_name'];
  }
  // If the sub_location changed, print it
  if ($row['sub_location'] != $current_subloc) {
    echo $row['sub_location_name'] . "\n";
    $current_subloc = $row['sub_location_name'];
  }
  echo $row['asset_name'] . "\n";      
}