SQL JOIN three tables, one with LIMIT 1

Question

I am working on a project for rental houses in MySQL. In a simplified view I have three tables. One holding the house data, one having the availability, the third one holding the prices.

The prices are the tricky part here as there may be different prices for different amount of people. E.g. renting house 1 (Blue house) with up to four persons is cheaper than with between 5 and 10 persons. Next to the price for the house, there often is an additional price per person.

Houses table:

+----+--------------+----------+
| id | name         | max_pers |
+----+--------------+----------+
|  1 | Blue house   |       10 |
|  2 | Red house    |        8 |
|  3 | Yellow house |        8 |
|  4 | Grey house   |        4 |
+----+--------------+----------+

Availability table:

+----+----------+------------+------------+
| id | house_id | from_date  | to_date    |
+----+----------+------------+------------+
|  1 |        1 | 2017-07-01 | 2017-07-14 |
|  2 |        1 | 2017-08-05 | 2017-08-19 |
|  3 |        2 | 2017-09-02 | 2017-09-16 |
|  4 |        3 | 2017-07-08 | 2017-07-14 |
|  5 |        4 | 2017-08-05 | 2017-08-12 |
|  6 |        4 | 2017-08-26 | 2017-09-02 |
+----+----------+------------+------------+

Prices table:

+----+----------+------------+------------+----------+--------+--------------+
| id | house_id | from_date  | to_date    | max_pers | price  | add_per_pers |
+----+----------+------------+------------+----------+--------+--------------+
|  1 |        1 | 2017-07-01 | 2017-07-08 |        4 | 110.00 |        15.00 |
|  2 |        1 | 2017-07-01 | 2017-07-08 |       10 | 140.00 |        10.00 |
|  3 |        1 | 2017-07-08 | 2017-07-14 |        4 | 120.00 |        15.00 |
|  4 |        1 | 2017-07-08 | 2017-07-14 |       10 | 150.00 |        10.00 |
|  5 |        1 | 2017-08-05 | 2017-08-12 |        4 | 130.00 |        20.00 |
|  6 |        1 | 2017-08-12 | 2017-08-19 |       10 | 180.00 |        15.00 |
|  7 |        2 | 2017-09-02 | 2017-09-09 |        8 | 210.00 |        30.00 |
|  8 |        2 | 2017-09-09 | 2017-09-16 |        8 | 220.00 |        30.00 |
|  9 |        3 | 2017-07-08 | 2017-07-14 |        6 | 300.00 |        40.00 |
| 10 |        3 | 2017-07-08 | 2017-07-14 |        8 | 360.00 |        50.00 |
| 11 |        4 | 2017-08-05 | 2017-08-12 |        4 |  80.00 |         null |
| 12 |        4 | 2018-08-26 | 2017-09-02 |        4 |  90.00 |         null |
+----+----------+------------+------------+----------+--------+--------------+

SQL dump of this test database can be downloaded here.

The goal

The query that I am trying to compose, should select houses where persons <= max_pers and where the house is available, then grab the ONE price from the prices table that has the lowest amount of allowed max_pers. E.g. when looking up the price for house 1 (Blue house) for 4 persons in the week of 2017-07-01, it should give 110 as the price. The query should return the house id, the price and the additional cost per person.

My efforts

I got as far as returning the house id and the price, grabbing only the correct price.

SELECT h.id AS house_id,
       (SELECT price
           FROM prices AS p
          WHERE p.house_id = h.id
            AND p.from_date = '2017-07-08'
            AND p.to_date = '2017-07-14'
            AND p.max_pers >= 6
         ORDER BY p.max_pers
         LIMIT 1) AS price
  FROM houses AS h
       INNER JOIN availability a
       ON h.id = a.house_id
 WHERE h.max_pers >= 6
   AND (a.from_date <= '2017-07-08'
       AND a.to_date >= '2017-07-14');

Which returns:

+----------+--------+
| house_id | price  |
+----------+--------+
|        1 | 150.00 |
|        3 | 360.00 |
+----------+--------+

So that works, but... I am stuck now on how to add the add_per_pers column from the prices table. I tried to add a JOIN instead of a select ... as. That way I can add the field, but then I cannot limit the data from prices to just the first one.

My best try:

SELECT h.id AS house_id, p.price, p.add_per_pers
  FROM houses AS h
       INNER JOIN availability a
       ON h.id = a.house_id
       INNER JOIN prices p
       ON h.id = p.house_id
 WHERE h.max_pers >= 6
   AND (a.from_date <= '2017-07-08'
       AND a.to_date >= '2017-07-14')
   AND p.from_date = '2017-07-08'
   AND p.to_date = '2017-07-14'
   AND p.max_pers >= 6;

Results in (the third row should not be there)

+----------+--------+--------------+
| house_id | price  | add_per_pers |
+----------+--------+--------------+
|        1 | 150.00 |        10.00 |
|        3 | 300.00 |        40.00 |
|        3 | 360.00 |        50.00 |
+----------+--------+--------------+

Desired result

Please help me to get this desired result:

+----------+--------+--------------+
| house_id | price  | add_per_pers |
+----------+--------+--------------+
|        1 | 150.00 |        10.00 |
|        3 | 360.00 |        40.00 |
+----------+--------+--------------+

SQL Fiddle available here

Answer 1

If i understood well you want only the record from Prices which has the minimum max_pers value.If so you can first find the minimum max person which is above the minimum,for each house_id and corresponding dates, and then JOIN.Something like this should do it

 SELECT h.id AS house_id, p.price, p.add_per_pers
  FROM houses AS h
       INNER JOIN availability a
       ON h.id = a.house_id
       INNER JOIN prices p
       ON h.id = p.house_id
       INNER JOIN (
                  SELECT MIN(max_pers) AS max_pers
                  FROM `Prices` WHERE  from_date = '2017-07-08'
                  AND to_date = '2017-07-14' AND max_pers>=6
                  GROUP BY house_id) temp 
      ON p.`max_pers` = temp.`max_pers` 
      WHERE h.max_pers >= 6
      AND (a.from_date <= '2017-07-08'
      AND a.to_date >= '2017-07-14')
      AND p.from_date = '2017-07-08'
      AND p.to_date = '2017-07-14'
      AND p.max_pers >= 6