CODEWITHSUNDEEP
mysqlsql
FreshSnow
FreshSnow

Reputation: 434

SQL JOIN three tables, one with LIMIT 1

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

Upvotes: 3

Views: 689

Answers (2)

Serg
Serg

Reputation: 22811

Provided scale and precision of prices are fixed this trick will work

SELECT house_id, pp, (pp DIV 10000)/100 price, (pp%10000)/100 add_per_pers
FROM (
SELECT h.id AS house_id,
       (SELECT CAST(price*1000000 + add_per_pers*100 AS UNSIGNED) pp
           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.price
         LIMIT 1) AS pp
  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')
  ) t;

Upvotes: 0

George Pant
George Pant

Reputation: 2117

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

Upvotes: 1

Related Questions