MySQL Query join

Question

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?

Are you trying to create the stored procedure for this?

Novice
– Novice

2013-07-02 13:59:02 +00:00
Commented Jul 2, 2013 at 13:59 — Novice
– Novice, Commented Jul 2, 2013 at 13:59

dethtron5000 · Accepted Answer · 2013-07-02 13:05:15Z

1

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

answered Jul 2, 2013 at 13:05

dethtron5000

10.9k1 gold badge33 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nitesh Mishra · Accepted Answer · 2013-07-02 13:33:39Z

0

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");

answered Jul 2, 2013 at 13:33

Nitesh Mishra

3111 silver badge10 bronze badges

Collectives™ on Stack Overflow

MySQL Query join

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related