0

Below are my tables

properties               bookings
==========               ==========
property_id              booking_id
price                    property_id 
city                     checkin_date
                         checkout_date

I have two tables, Properties and Bookings for a rental site. I want to do a search by check in date, check out date, price and location. It will check the availability from the foreign table bookings via the property_id FK.

Basically I want it to be something like:

SELECT property_id,price,city FROM properties 
WHERE 
price > 200,
city = "Toronto",
LEFT JOIN (
   SELECT postid, COUNT( * ) AS total
   FROM bookings
   WHERE checkin_date *** SOMEHTING HERE ****

I am sure the above is incorrect already. Whether if I am to use left join or inner join.

halfer
  • 19,824
  • 17
  • 99
  • 186
Eddsters
  • 458
  • 1
  • 4
  • 16
  • Do the `join` then limit the results with the `where` clause. Maybe `SELECT property_id,price,city FROM properties as p inner join bookings as b on b.property_id = p.property_id WHERE p.price > 200, p.city = "Toronto"` If you are just asking generally about the different join types see: http://stackoverflow.com/questions/5706437/whats-the-difference-between-inner-join-left-join-right-join-and-full-join Also this appears to only be a mysql question. – chris85 Jul 17 '16 at 21:55
  • Oh, for the date add and `? between b.checkin_date and b.checkout_date` or something like that and bind the user date. – chris85 Jul 17 '16 at 22:05
  • Your join needs to be before your where clause, and where clauses need to be concatenated with `AND` or `OR`, not commas. Why not try this in a real database? – halfer Jul 17 '16 at 22:47

1 Answers1

1
SELECT DISTINCT p.property_id, p.price, p.city 
FROM properties AS p
LEFT JOIN bookins AS b ON p.property_id = b.property_id
WHERE p.price > 200
AND p.city = 'Toronto'
AND (b.checkin_date >= '?' OR b.checkout_date <= '?')

replace ? with your search date

SIDU
  • 2,258
  • 1
  • 12
  • 23