mysql - 无法从mysql中的三个表中获取不匹配的行
问题描述
我无法从 mysql 的三个表中获得不匹配的行。rd 表有 3 行的venueid 88。但我的查询只返回两行。我想要的输出是:
bookingId|bookingMeetingRoomId|bookingVenueId|venueId|rdId|morningTotalGuest|shiftId|
|98 |80 |88 |88 |80 |99 |137|
|null |null |null |88 |81 |null |130|
|null |null |null |88 |82 |null |null|
SELECT a.booking_id,a.booking_meeting_room_id,a.booking_venue_id,rd.venue_id,rd.rd_id,
rd.rd_img_name,rd.rd_rhname, rd.rd_max_capacity,
-- shift.shift_id,
(
SELECT SUM(booking_total_guest)
FROM dv_bookings
WHERE booking_shift_name LIKE '%morning%' AND booking_venue_id = 88 AND booking_checkin = '2018-04-06' AND a.booking_meeting_room_id=dv_bookings.booking_meeting_room_id) AS 'morning_total_guest',
(
SELECT SUM(booking_total_guest)
FROM dv_bookings
WHERE booking_shift_name LIKE '%dinner%' AND booking_venue_id = 88 AND booking_checkin = '2018-04-06' AND a.booking_meeting_room_id=dv_bookings.booking_meeting_room_id) AS 'dinner_total_guest',
(
SELECT SUM(booking_total_guest)
FROM dv_bookings
WHERE booking_shift_name LIKE '%lunch%' AND booking_venue_id = 88 AND booking_checkin = '2018-04-06' AND a.booking_meeting_room_id=dv_bookings.booking_meeting_room_id) AS 'lunch_total_guest'
FROM dv_bookings a
RIGHT JOIN
(
SELECT rd_id, rd_img_name, rd_rhname,rd_max_capacity, rd_room_size,venue_id
FROM dv_room_details
WHERE venue_id = 88) rd ON
a.booking_meeting_room_id = rd.rd_id
AND a.booking_venue_id = 88 AND a.booking_checkin= '2018-04-06'
right JOIN
(
SELECT shift_id,shift_room_id,shift_venue_id,shift_morning_start_time,shift_morning_end_time,shift_lunch_start_time,shift_lunch_end_time,shift_dinner_start_time,shift_dinner_end_time
FROM dv_venue_shift_time_table
WHERE shift_venue_id = 88 AND shift_day_name='Fri') shift
-- WHERE shift_id is null) shift
ON rd.rd_id = shift.shift_room_id
-- on rd.rd_id = a.booking_meeting_room_id
-- on shift.shift_room_id = a.booking_meeting_room_id
-- AND a.booking_venue_id = 88 AND a.booking_checkin= '2018-04-06'
GROUP BY a.booking_meeting_room_id;
解决方案
推荐阅读
- ios - iOS 自动更新订阅和收据验证说明
- docker - 在 docker compose 中更改容器端口
- python - ValueError 无法将字符串转换为浮点数:''
- powerbi - Power Query - 一步查找和替换,而不是几个
- ios - NativeScript Firebase onMessageReceivedCallback 不会在 iOS 上触发
- python - 从 iTerm 访问 Jupyter 终端
- java - 在 Feign Client 中发送多个密钥
- r - 如何使用 qqplot() 并将输出分配给对象?
- c# - 有没有办法为 VS2015 实现类似的 Raise Property Changed?
- xslt - 缺少 XSLT 的后代节点