0

I have this query which is meant to output the guest's name, how much they paid and the description for the associated item they have paid for.

SELECT g.first_name,  ex.pmt_method, ex.pmt_date, 
ex.pmt_amount AS paid, e.description
FROM extras_payments_test ex
LEFT JOIN booking b
ON ex.room_booking = b.booking_id

LEFT JOIN guest_test g
ON b.guest_id = g.id

LEFT JOIN extra_test e
ON  e.booking_id =b.booking_id 

WHERE g.first_name = 'Caroline';

first_name     pmt_method    pmt_date      paid    description 
Caroline   |   cash     |   2016-12-01  |  1.87 |  Breakfast x 2
Caroline   |   card     |   2016-12-01  |  8.0  |  Breakfast x 2
Caroline   |   cash     |   2016-12-01  |  1.87 |  Phone calls £1.87
Caroline   |   card     |   2016-12-01  |  8.0  |  Phone calls £1.87

This output is not correct because it duplicates the rows and makes a mistake in appending the column from the extra_test table. I have exprimented with LEFT and RIGHT joins, e.g. 'RIGHT JOIN extra_test e' however, I cannot get the desired result.

Below is my desired output.

Caroline  |   cash   |    2016-12-01  |  1.87  | Phone calls £1.87
Caroline  |   card   |    2016-12-01  |  8.0   | Breakfast x 2

Here is the reproducible code for creating these database tables in MariaDB:

CREATE TABLE `guest_test` (
  `id` int(11) NOT NULL,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  `address` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO `guest_test` VALUES (1413,'Caroline','Nokes','Romsey and Southampton North'),
(1414,'Mark','Pritchard','The Wrekin');

CREATE TABLE `booking_test` (
  `booking_id` int(11) NOT NULL,
  `booking_date` date DEFAULT NULL,
  `room_no` int(11) DEFAULT NULL,
  `guest_id` int(11) NOT NULL,
  `occupants` int(11) NOT NULL DEFAULT '1',
  `room_type_requested` varchar(6) DEFAULT NULL,
  `nights` int(11) NOT NULL DEFAULT '1',
  `arrival_time` varchar(5) DEFAULT NULL,
  PRIMARY KEY (`booking_id`),
  KEY `room_no` (`room_no`),
  KEY `guest_id` (`guest_id`),
  KEY `room_type_requested` (`room_type_requested`,`occupants`),
  CONSTRAINT `booking_ibfk_1` FOREIGN KEY (`room_no`) REFERENCES `room_test` (`id`),
  CONSTRAINT `booking_ibfk_2` FOREIGN KEY (`guest_id`) REFERENCES `guest_test` (`id`),
  CONSTRAINT `booking_ibfk_3` FOREIGN KEY (`room_type_requested`) REFERENCES `room_type_test` (`id`),
  CONSTRAINT `booking_ibfk_4` FOREIGN KEY (`room_type_requested`, `occupants`) REFERENCES `rate_test` (`room_type`, `occupancy`)
);
INSERT INTO `booking_test` VALUES (5350,'2016-12-01',210,1413,2,'double',1,'14:00'),
(5223,'2016-11-30',203,1414,2,'double',5,'20:00');


CREATE TABLE `extra_test` (
  `extra_id` int(11) NOT NULL,
  `booking_id` int(11) DEFAULT NULL,
  `description` varchar(50) DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`extra_id`)
);
INSERT INTO `extra_test` VALUES (535001,5350,'Breakfast x 2',18.00),(535002,5350,'Phone Calls £1.87',1.87),(522301,5223,'Breakfast x 2',18.00);



CREATE TABLE `room_test` (
  `id` int(11) NOT NULL,
  `room_type` varchar(6) DEFAULT NULL,
  `max_occupancy` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `room_type` (`room_type`),
  CONSTRAINT `room_ibfk_1` FOREIGN KEY (`room_type`) REFERENCES `room_type_test` (`id`)
);

INSERT INTO `room_test` VALUES (202,'single',1),(203,'double',2),(204,'double',2);


CREATE TABLE `room_type_test` (
  `id` varchar(6) NOT NULL,
  `description` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO `room_type_test` VALUES ('double','Fabulously appointed double room.'),('single','Luxury accomodation suitable for one person.');

CREATE TABLE `rate_test` (
  `room_type` varchar(6) NOT NULL DEFAULT '',
  `occupancy` int(11) NOT NULL DEFAULT '0',
  `amount` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`room_type`,`occupancy`),
  CONSTRAINT `rate_ibfk_1` FOREIGN KEY (`room_type`) REFERENCES `room_type_test` (`id`)
);

INSERT INTO `rate_test` VALUES ('double',1,56.00),('double',2,72.00),('single',1,48.00);


CREATE TABLE `extras_payments_test` (
  `extra_pmt_id` int(11) NOT NULL,
  `room_booking` int(11) DEFAULT NULL,
  `pmt_date` date DEFAULT NULL,
  `pmt_method` varchar(30) NOT NULL,
  `pmt_amount` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`extra_pmt_id`),
  KEY `room_booking` (`room_booking`),

  CONSTRAINT `ext_pmt_tibfk_1` FOREIGN KEY (`room_booking`) REFERENCES `booking_test` (`booking_id`)
);

INSERT INTO `extras_payments_test` VALUES (604, 5350, '2016-12-01', 'Cash', 1.87),(605, 5350, '2016-12-01', 'Card', 8.00);

Can someone help me with this? I do not understand why left joins do not work.

1 Answer 1

0

I think there is something missing in your schema. extras_payments_test relates to booking_test, and extra_test also relates to booking_test. But how can you know which payment is related to which extra?

When you write:

FROM extras_payments_test ex
LEFT JOIN booking b ON ex.room_booking = b.booking_id

It means: for each record or extras_payments_test, give me matching records from booking matching ex.room_booking = b.booking_id. If there is nothing matching in booking, give me record from extra_payments_test anyway. If there is many rows in booking matching the one in extras_payments_test, you'll get a duplicate from extras_payments_test. In this specific case, a LEFT JOIN doesn't make sense, since extras_payments_test can only exist if there this a corresponding booking. An INNER JOIN would be more appropriate, but it will give the same result.

Then, you have this JOIN in your query:

LEFT JOIN guest_test g
ON b.guest_id = g.id

Same idea as the booking table, a booking can't exist without a guest and there is only 1 guest associated with the booking. Again, an INNER JOIN would be more appropriate.

Finally, you have this join:

LEFT JOIN extra_test e
ON  e.booking_id =b.booking_id 

Which means: if there is any extras associated with that booking, return them.

Let's rewrite this query in a more logical way (but we still get the duplicates):

SELECT g.first_name,  ex.pmt_method, ex.pmt_date, 
ex.pmt_amount AS paid, e.description
FROM booking b
    INNER JOIN guest_test g ON b.guest_id = g.id
    INNER JOIN extras_payments_test ex ON ex.room_booking = b.booking_id
    LEFT JOIN extra_test e ON  e.booking_id =b.booking_id 
WHERE g.first_name = 'Caroline';

Translated in plain English: Give me all extras payment related to bookings done by Caroline. If there is extras related to these bookings, return them too.

So, you have multiple extras and multiple payments for a booking, but your schema doesn't have anything to relate an extra payment to the corresponding extra, so you get a cartesian product. If you had an extra_id column in extras_payments_test, you would be able to get the result you want like this:

SELECT g.first_name,  ex.pmt_method, ex.pmt_date, 
ex.pmt_amount AS paid, e.description
FROM booking b
    INNER JOIN guest_test g ON b.guest_id = g.id
    INNER JOIN extras_payments_test ex ON ex.room_booking = b.booking_id
    LEFT JOIN extra_test e ON  e.booking_id =b.booking_id AND ex.extra_id=e.extra_id
WHERE g.first_name = 'Caroline';
2
  • thank you for taking the time to write this! so I could just add the extra_id column in extras_payments_test as a foreign key?
    – Bluetail
    Aug 25, 2022 at 14:44
  • If one payment can be applied to only one extra, yes. If one payment can cover many extras, then you could do the other way around: payment_id in extra_test table. Aug 25, 2022 at 19:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.