I'm not sure what kind of join I need as I'm not familiar with trying to overlap data in such a way or if it's even feasible.
I have two tables which both share a similar set of data and are both related to a 3rd parent table thru Room_id.
I have a table called Room_rates which stores average prices for each room (room_id)
+-------+---------+-----------+-------+--------+---------------------------+---------------------------+
| id | room_id | dayofweek | price | source | created_at | updated_at |
+-------+---------+-----------+-------+--------+---------------------------+---------------------------+
| 87936 | 2517 | 0 | 14.58 | 1 | 2010-02-22 17:47:14 +0100 | 2010-02-22 17:47:14 +0100 |
| 87937 | 2517 | 1 | 14.58 | 1 | 2010-02-22 17:47:14 +0100 | 2010-02-22 17:47:14 +0100 |
| 87938 | 2517 | 2 | 14.52 | 1 | 2010-02-22 17:47:14 +0100 | 2010-02-22 17:47:14 +0100 |
| 87939 | 2517 | 3 | 14.52 | 1 | 2010-02-22 17:47:14 +0100 | 2010-02-22 17:47:14 +0100 |
| 87940 | 2517 | 4 | 14.52 | 1 | 2010-02-22 17:47:15 +0100 | 2010-02-22 17:47:15 +0100 |
| 87941 | 2517 | 5 | 14.4 | 1 | 2010-02-22 17:47:15 +0100 | 2010-02-22 17:47:15 +0100 |
| 87942 | 2517 | 6 | 14.63 | 1 | 2010-02-22 17:47:15 +0100 | 2010-02-22 17:47:15 +0100 |
+-------+---------+-----------+-------+--------+---------------------------+---------------------------+
And a table called Availables which has rates for specific dates
+--------+-------+-------+------------+---------+---------------------------+---------------------------+--------+
| id | price | spots | bookdate | room_id | created_at | updated_at | source |
+--------+-------+-------+------------+---------+---------------------------+---------------------------+--------+
| 221389 | 14.3 | 1 | 2010-03-01 | 2517 | 2010-02-21 22:31:06 +0100 | 2010-02-21 22:31:06 +0100 | 1 |
| 221390 | 14.3 | 1 | 2010-03-02 | 2517 | 2010-02-21 22:31:06 +0100 | 2010-02-21 22:31:06 +0100 | 1 |
| 221391 | 14.3 | 1 | 2010-03-03 | 2517 | 2010-02-21 22:31:06 +0100 | 2010-02-21 22:31:06 +0100 | 1 |
| 221392 | 14.3 | 1 | 2010-03-04 | 2517 | 2010-02-21 22:31:06 +0100 | 2010-02-22 17:47:19 +0100 | 1 |
| 221393 | | 0 | 2010-03-05 | 2517 | 2010-02-21 22:31:06 +0100 | 2010-02-22 17:47:19 +0100 | 1 |
| 221394 | | 0 | 2010-03-06 | 2517 | 2010-02-21 22:31:06 +0100 | 2010-02-22 17:47:19 +0100 | 1 |
| 228185 | | 0 | 2010-03-07 | 2517 | 2010-02-22 17:47:19 +0100 | 2010-02-22 17:47:19 +0100 | 1 |
| 228186 | 14.3 | 1 | 2010-03-08 | 2517 | 2010-02-22 17:47:19 +0100 | 2010-02-22 17:47:19 +0100 | 1 |
| 228187 | 14.3 | 1 | 2010-03-09 | 2517 | 2010-02-22 17:47:19 +0100 | 2010-02-22 17:47:19 +0100 | 1 |
| 228188 | 14.3 | 1 | 2010-03-10 | 2517 | 2010-02-22 17:47:19 +0100 | 2010-02-22 17:47:19 +0100 | 1 |
+--------+-------+-------+------------+---------+---------------------------+---------------------------+--------+
Currently I am using two separate search results, either current Availables
data for a specific date range exists or it doesn't. If it doesn't I use a back up query using only Room_rate
averages.
I would like to use Availables where it has available prices, but somehow join Room_rates to fill in the blanks if the price is unavailable or possibly there is no record at all.
How could I accomplish this?