CODEWITHSUNDEEP
mysqlsqljoin
jah
jah

Reputation: 1305

Between a MIN and MAX date in joined table

I have a table called Booking and a table called FacilityBooking. A booking is a composition of facility bookings, a one to many relation. The date and time of the booking is determined by the lowest start date, and the highest end date of the facility bookings that belongs to it.

I want to pull some statistics of how many private and how many business bookings there has been between two dates.

CREATE TABLE `Booking` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `comments` varchar(255) DEFAULT NULL,
  `createdBy` varchar(255) DEFAULT NULL,
  `customerName` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `isPaid` bit(1) DEFAULT NULL,
  `isPrivateClient` bit(1) DEFAULT NULL,
  `needsPermission` bit(1) DEFAULT NULL,
  `phoneNumber` varchar(255) DEFAULT NULL,
  `referenceNumber` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;



# Dump of table FacilityBooking
# ------------------------------------------------------------

CREATE TABLE `FacilityBooking` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `comments` varchar(2000) DEFAULT NULL,
  `from` datetime DEFAULT NULL,
  `to` datetime DEFAULT NULL,
  `bookablePlace_id` int(11) DEFAULT NULL,
  `booking_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_2tv9w7g5vyx9po8vs6ceogldb` (`bookablePlace_id`),
  KEY `FK_n17h188ecbdos5lsva51b8j29` (`booking_id`),
  CONSTRAINT `FK_n17h188ecbdos5lsva51b8j29` FOREIGN KEY (`booking_id`) REFERENCES `Booking` (`id`),
  CONSTRAINT `FK_2tv9w7g5vyx9po8vs6ceogldb` FOREIGN KEY (`bookablePlace_id`) REFERENCES `BookablePlace` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I have created an sqlfiddle: http://sqlfiddle.com/#!9/7ae95/2

And this is what i have so far:

SELECT
    CASE isPrivateClient
        WHEN 0 THEN "business"
        WHEN 1 THEN "private"
    END AS clientType,
    count(isPrivateClient) as count
FROM
    Booking
GROUP BY
    isPrivateClient

So what i need from here is to join the facilitybookings and search between the lowest from date and the highest to date.

Hope someone can help me :)

Upvotes: 1

Views: 48

Answers (2)

Remy Grandin
Remy Grandin

Reputation: 1686

If you want to only include "full" Booking in which all FacilityBooking are between 2 date, something like this should do the trick : 

SELECT clientType, count(bookId)
FROM (
    SELECT
        b.id as bookId,
        CASE b.isPrivateClient
            WHEN 0 THEN "business"
            WHEN 1 THEN "private"
        END AS clientType,
        Min(fb.from) as minFrom,
        Max(fb.to) as maxTo
    FROM
        Booking b
        INNER JOIN FacilityBooking fb ON b.id = fb.booking_id
    GROUP BY bookId
  ) tbl

WHERE minFrom >= '2015-05-22' -- Min Date
AND maxTo <= '2015-05-24' -- Max Date

GROUP BY
  clientType

Upvotes: 1

Amit
Amit

Reputation: 46351

Join the FacilityBooking table and filter using WHERE:

SELECT
    CASE isPrivateClient
        WHEN 0 THEN "business"
        WHEN 1 THEN "private"
    END AS clientType,
    count(FacilityBooking.id) as count
FROM
    Booking INNER JOIN FacilityBooking ON
    Booking.id = FacilityBooking.booking_id
-- Between 2015-05-01 AND 2015-06-01 INCLUSSIVE'
WHERE FacilityBooking.from <= '2015-06-01' AND FacilityBooking.to >= '2015-05-01'
GROUP BY
    isPrivateClient

fixed fiddle

Upvotes: 1

Related Questions