Rewrite MySQL query

  • Posted:
  • Proposals: 14
  • Remote
  • #775657
  • Awarded
Derek P.
Ketan S.Excellence ICT ™.Muhammad M.Yendra G. + 9 others have already sent a proposal.
  • 0

Description

Experience Level: Entry
General information for the business: holiday rental websites
Database management system (DBMS): MySQL
Description of requirements/functionality: Rewrite MySQL query to add additional table JOIN. Query current joins 3 tables:

SELECT a.propertyid,a.banner,a.name,a.type,a.occupancy,a.bedrooms,a.bathrooms,b.image AS leadimage,b.alt,b.height,b.width
FROM property a LEFT JOIN images b ON a.propertyid = b.propertyid
WHERE a.status = 'A' AND b.lead = 'Y' AND type = 'V' AND NOT EXISTS(SELECT propertyid FROM booking WHERE booking.propertyid = a.propertyid AND booking.checkin '2015-06-12')
ORDER BY a.updated DESC,a.propertyid ASC LIMIT 0,12

The query takes search criteria based on property table and left joins with image table to only return properties with an image then queries booking table to only return properties that DONT have bookings between two dates (checkin and checkout) passed in.

I wish to add a 4th table called rate.

This table stores all the rates for a property and what dates those rates apply to so looking to return the rate value which applies to the checkin date passed in.
Extra notes: Can provide more details on request. Can provide the mysql DDL populated with data so that this can be built and tested remotely.

This will not be a time consuming task but must be someone with strong sql skills.

Thanks

New Proposal

Create an account now and send a proposal now to get this job.

Sign up

Clarification Board Ask a Question

    There are no clarification messages.