Rewrite MySQL query
- or -
Post a project like this3257
£10(approx. $13)
- Posted:
- Proposals: 12
- Remote
- #775657
- Awarded
C#, VB, .Net, Java, JSP, XML, HTML, Jquery, Ajax, PHP, MySQL, Codeigniter, API, Weebly, Wix, Python, Ruby, Node.js, SQL, Oracle
Dhaka
Responsive Website | | Flash | Logo Designing | Wordpress | Magento | PHP | MySql | ASP.net | MsSql | Jquery | Ajax | CSS | HTML5 | AWS
Mumbai
20+ Years of experience | Your Trusted Tech Partner for Critical Websites & Web Apps
Sofia
12965115222160188243108536710546324550406904606924199933631937855357179
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
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
Ewan M.
99% (31)Projects Completed
35
Freelancers worked with
28
Projects awarded
76%
Last project
17 Sep 2018
United Kingdom
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
There are no clarification messages.
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies