SQL JOIN three tables, one with LIMIT 1
I am working on a house rental project in MySQL. In a simplified way, I have three tables. One of them contains data about the house, one of which has availability, the third - prices.
The prices here are tricky as there may be different prices for different people. For example. Renting a house 1 (Blue House) with four people is cheaper than 5 to 10 people. There is often an additional price per person next to the price of a house.
House 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 |
+----+----------+------------+------------+
Price 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 |
+----+----------+------------+------------+----------+--------+--------------+
The SQL dump of this test database can be downloaded here .
purpose
The query I'm trying to create should select houses where there are persons <= max_pers and where the house is available, then take the ONE price from the price table that has the smallest number of max_pers allowed. For example. when looking for the price of house 1 (Blue House) for 4 people per week 2017-07-01, it should give 110 as the price. The request must return the house ID, price and additional cost per person
My efforts
I got to the house number and price, snapping just the right 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 it works, but ... I am now stuck on how to add the add_per_pers column from the pricing table. I tried adding JOIN instead of select ... as. This way I can add a field, but then I cannot restrict data from prices to only the first one.
My best attempt:
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 (shouldn't be on the third line)
+----------+--------+--------------+
| 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 get this desired output:
+----------+--------+--------------+
| house_id | price | add_per_pers |
+----------+--------+--------------+
| 1 | 150.00 | 10.00 |
| 3 | 360.00 | 40.00 |
+----------+--------+--------------+
source to share
If I understood well, you only need the entry from Prices
which has the minimum value max_pers
. If you can first find the minimum maximum person that is above the minimum for each house_id and corresponding dates, 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
source to share
Provided the scale and accuracy of prices are corrected, 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;
source to share