Join 2 tables with filter on date range and number

Question

I'm trying to get the results(entire rows) of a property table using the result of a JOIN of 2 tables. A "booking_tbl" and "property_tbl". The first condition is that the property not be already booked within a specified date period (startDate, endDate). The second condition is that a number (property_tbl.max_guest) must be equal to or larger than another specified number (variable: num_guest) to accommodate all guests.

So far what I've managed to achieve is:

SELECT * FROM property_tbl 
INNER JOIN booking_tbl ON booking_tbl.propertyId = property_tbl.id 
WHERE NOT booking_tbl.endDate > '17-10-24' AND booking_tbl.startDate < '17-10-31';

The above returns all of the available properties which occur in the booking_tbl only, The listings from property_tbl which have not previously been booked are omitted.

After some research on this I think I should be using a LEFT JOIN? What is the difference between "INNER JOIN" and "OUTER JOIN"?

But unfortunately I'm having difficulty formatting the query and understanding how to add another JOIN and filter the number range.

Some help with this query would be greatly appreciated.

Thank you in advance

score 1 · Accepted Answer · answered Oct 12 '16 at 22:44

1

To find rows that aren't matched in another table, you can use the LEFT JOIN/NULL pattern. When you use a LEFT JOIN, the conditions on the child table are put into the ON clause. Conditions on the parent table are put into the WHERE clause.

SELECT p.*
FROM property_tbl AS p
LEFT JOIN booking_tbl AS b
ON p.id = b.propertyID AND booking_tbl.endDate > '17-10-24' AND booking_tbl.startDate < '17-10-31'
WHERE b.propertyID IS NULL
AND p.max_guest > @num_guest

answered Oct 12 '16 at 22:44

Barmar

741,623
53
500
612

That's great, returns expected results and is easy to understand. Thanks very much for the help :) – webstudent Oct 13 '16 at 08:40

score 0 · Answer 2 · answered Oct 12 '16 at 22:40

You need to select the properties which have sufficient capacity and, are either (A) Not booked or (B) Booked but outside the range you are querying for, below query should work:

select id from property_tbl p
where 
p.capacity > 5 and
not exists (
  select id from booking_tbl where p_id = p.id)

union

select id from property_tbl p
where 
p.capacity > 5 and
p.id not in (
  select distinct id from booking_tbl
  where 
        startDate between '2016-09-17' and '2016-09-22'
    OR 
        endDate between '2016-09-17' and '2016-09-22'
)

Here is the SQL Fiddle.

Thanks for your response, the above is great, just a little complex and moves away from joins. I felt more comfortable understanding and wrapping my head around the accepted answer, thanks for taking the time though — webstudent, Oct 13 '16 at 08:44

Join 2 tables with filter on date range and number

2 Answers2