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

Just order your results by location then sublocation, and structure the output as desired in your application code (detecting a change in location/sublocation by comparison with the previous record). — eggyal, Jul 23 '12 at 11:15

Michael Berkowski · Accepted Answer · 2012-07-23T11:36:13.277

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";      
}

Thanks Michael, looks like this will be a lot simpler than I first thought. How do I loop my code so I only get the location and sub_location when it changes? — Ian, Jul 23 '12 at 11:26
Thanks Michael! This is going to do the job nicely. Thanks again. — Ian, Jul 23 '12 at 14:51

Zane Bien · Answer 2 · 2012-07-23T11:44:30.933

You can display a known-depth hierarchical structure with this solution:

SELECT a.name
FROM
(
    SELECT 
        CONCAT('- - - - - - - - - -> ', a.asset_name) AS name,
        CONCAT(c.location_key, c.location_name, b.sub_location_name, a.asset_name) AS orderfactor
    FROM asset a
    INNER JOIN sub_location b ON a.sub_location_key = b.sub_location_key
    INNER JOIN location c ON b.location_key = c.location_key

    UNION ALL

    SELECT 
        CONCAT('- - - - -> ', a.sub_location_name),
        CONCAT(b.location_key, b.location_name, a.sub_location_name)
    FROM sub_location a
    INNER JOIN location b ON a.location_key = b.location_key

    UNION ALL

    SELECT
        location_name,
        CONCAT(location_key, location_name)
    FROM location
) a
ORDER BY a.orderfactor

For more levels, you could add in more unions to the subselect.

SQLFiddle Demo

Example result-set:

Ground Floor
-----> Garage
----------> Radiator
-----> Kitchen
----------> Cooker
----------> Fridge
First Floor
-----> Bedroom
----------> Radiator
----------> Taps
Second Floor
-----> Bathroom
----------> Shower
----------> Taps
----------> Toilet
Third Floor

score 1 · Answer 3 · answered Jul 23 '12 at 11:32

If it is in MySQL5

SELECT L.location_name, GROUP_CONCAT(DISTINCT S.sub_location_name), GROUP_CONCAT(A.asset_name) 
FROM location L
INNER JOIN sub_location S ON L.location_key = S.location_key
INNER JOIN asset A ON A.sub_location_key = S.sub_location_key
GROUP BY S.sub_location_name ORDER BY L.location_key

http://sqlfiddle.com/#!2/db932/9

MySQL subquery on each record in group by

3 Answers3

SQLFiddle Demo