views:

374

answers:

2

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?

+1  A: 

This is not the case for a join. A join will match data from a table to data from another table. For example, for matching room and room_rates you would use a join. But you don't want to match Available and Room_rates. You want to get the rate from Available or - if not found in Avaiable- from Room_rates.

So, you really have to first run a query on Available and then a query on Room_rates if needed.

You could get the first result returned by a query that is something like:

select price from Availables where room_id=[id] and bookdate=[date]
union
select price from room_rates where room_id=[id] and dayofweek=[day of week]

But I would not really recommend that because the second "select" would be run even if not needed. So it's better to run the first select and then the second select only if needed.

Bruno Rothgiesser
+3  A: 

The COALESCE function could be combined with a join to give you what you want:

SELECT COALESCE(a.price, rr.price) 
  FROM Availables AS a
  FULL OUTER JOIN room_rates AS rr
    ON a.room_id = rr.room_id
  WHERE a.room_id=[id]
    AND a.bookdate=[date]
    AND rr.dayofweek=[dayofweek]

You'll need indices on the appropriate columns for this to be performant. You'll also need to compare the execution plan of this statement against alternatives (such as the UNION suggested by Bruno) to see if the join is worth it.

outis