ansaurus

Question

Answer 1

+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 2010-02-25 13:50:53

Answer 2

+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 2010-02-25 14:05:37

ansaurus

tags:

views:

answers:

SQL natural join POSTGRES

related questions