CODEWITHSUNDEEP
phpmysqlcakephp
Zaphod Beeblebrox
Zaphod Beeblebrox

Reputation: 562

MySQL Query join

I'm trying to build a website which controls a portfolio of rental properties. The site owner can create a listing for a property. The property has a default price per night but also includes the ability to set a special price, for example, for August.

The sites users can then go onto the site and input a date range and search for listings which are available over that time period. I also need at that stage to see if there's a special price involved to replace the default price.

So the tables I have are:

listings
    id
    default_price
    etc...

bookings 
   id
   listing_id
   checkin
   checkout

special_prices
   id
   listing_id
   from
   to
   price

But I'm useless at sql queries and I can't figure out what the query would look like to listings which had no bookings during the time period entered and which gets the special_price if it exists.

I'm using cakephp if that makes the query easier to build. Can anyone help me out?

EDIT Hey guys, I'm still trying to figure this out at the minute I'm just concentrating on getting listings which don't have bookings which clash with the requested dates, and I'll sort out the special prices stuff after that

At the minute I have:

$conditions = array(
    "AND" => array(
        "OR" => array(
                    "Listing.address_one LIKE" => "%".$area."%",
                    "Listing.address_two LIKE" => "%".$area."%",
                    "Listing.city LIKE" => "%".$area."%",
                    "Listing.postcode LIKE" => "%".$area."%",
                    "Listing.title LIKE" => "%".$area."%",
                    ),
                    )
                );

$this->Listing->bindModel(array('hasMany' => array('Booking' => array('conditions' =>array('Booking.checkin BETWEEN ? AND ?' => array($to, $from))))));
$data = $this->paginate('Listing', $conditions);
$this->set('data', $data);

This gets all results which match the $area keyword in the Listing cols, but seems to completely ignore the Bookings table. I've tried putting in NOT BETWEEN to see if it made a difference and it still just returns whatever matches $area and seemingly ignores the Bookings between the requested dates.

Anyone any ideas?

Upvotes: 0

Views: 102

Answers (2)

Nitesh Mishra
Nitesh Mishra

Reputation: 311

This is done using correlated sub-query.

SELECT li.id FROM listings li WHERE 
 0=(SELECT COUNT(*) FROM bookings bo WHERE bo.listing_id=li.id
 AND bo.checkin="START DATE" AND bo.checkout="END DATE");

Upvotes: 0

dethtron5000
dethtron5000

Reputation: 10841

There are several ways of doing this but LEFT JOINing the bookings table and searching for null results is one way of going about it. Assuming checkin = a booking date...

SELECT 
* 
FROM listings l
LEFT JOIN bookings b ON b.listing_id = l.id AND b.checkin BETWEEN YOUR START DATE AND YOUR_END_DATE
WHERE b.id IS NULL

Upvotes: 1

Related Questions