MySQL GROUP BY with MIN - incorrect column data

I have looked here: Selecting all corresponding fields using MAX and GROUP BY and similar pages on SO but I cannot seem to get all my fields to line up properly.

I feel like I'm at the cusp of figuring this out but maybe I'm heading down the wrong path and need to look at this differently.

What I want is the unit with the lowest rent per property name per bedroom count that have the merge flag set to 1.

My SQL Fiddle: http://sqlfiddle.com/#!2/881c41/2

The image above was obtained with this query:

SELECT ru.id, run.name, ru.rent, ru.bedrooms
FROM rental_units AS ru
JOIN rental_unit_names AS run
on run.id = ru.name_id
WHERE run.merge = 1
ORDER BY run.name ASC, ru.bedrooms ASC, ru.rent ASC

The image above is the result of this query:

SELECT ru.id, run.name, ru.rent, MIN(ru.rent) AS min_rent, ru.bedrooms
FROM rental_units AS ru
JOIN rental_unit_names AS run
on run.id = ru.name_id
WHERE run.merge = 1
GROUP BY ru.name_id, ru.bedrooms
ORDER BY run.name ASC, ru.bedrooms ASC, ru.rent ASC, ru.id ASC

For the most part all looks fine and dandy until you look at row 4. The rent values do not line up and the id should be 6 not 5.

The image below is my desired result.

:: EDIT 1 ::

Do I need to create a linking table with 2 columns that has the rental unit id in one column and the rental unit name id in the other column? Or at least do this as a derived table somehow?

In general, unless you're trying to perform some sort of MySQL "magic" you should always group by every non-aggregate, non-constant column in your SELECT list.

In your case, the best approach is to get a list of (name, # bedrooms, minimum rent), and then find all the rows that match these values - in other words, all rows whose (name, # bedrooms, rent) match the list with the minimum rent:

SELECT ru.id, run.name, ru.rent, ru.bedrooms
FROM rental_units ru
JOIN rental_unit_names run ON run.id = ru.name_id
WHERE run.merge = 1
  AND (run.name, ru.bedrooms, ru.rent) IN (
    SELECT inrun.name, inru.bedrooms, MIN(inru.rent)
    FROM rental_units inru
    JOIN rental_unit_names inrun ON inrun.id = inru.name_id
    WHERE inrun.merge = 1
    GROUP BY inrun.name, inru.bedrooms)

This query will give all lowest-rent units by name/bedrooms. The sample data has ties for lowest in a couple of places. To include only one of the "tied" rows (the one with the lowest rental_units.id, try this instead - the only change is the MIN(ru.id) on the first line and the addition of an overall GROUP BY on the last line:

SELECT MIN(ru.id) AS ru_id, run.name, ru.rent, ru.bedrooms
FROM rental_units ru
JOIN rental_unit_names run ON run.id = ru.name_id
WHERE run.merge = 1
  AND (run.name, ru.bedrooms, ru.rent) IN (
    SELECT inrun.name, inru.bedrooms, MIN(inru.rent)
    FROM rental_units inru
    JOIN rental_unit_names inrun ON inrun.id = inru.name_id
    WHERE inrun.merge = 1
    GROUP BY inrun.name, inru.bedrooms)
GROUP BY run.name, ru.rent, ru.bedrooms