mysql - 收藏夹离开加入

Question

我有一个网站，您可以在其中查看 field 。当您单击单个 field 时，它会显示一个弹出窗口。有一个收藏夹按钮，可以激活也可以不激活。此代码位于此弹出窗口的 ajax 中

SELECT * FROM venue_datetime
LEFT JOIN venue_calendar ON venue_datetime.calendar = venue_calendar.calendar_id
LEFT JOIN venue_events ON venue_calendar.event = venue_events.event_id
LEFT JOIN venue ON venue_events.venue = venue.venue_id
LEFT JOIN favorites ON venue.venue_id = favorites.favorites_venue_id OR venue.venue_id is null
LEFT JOIN users ON favorites.favorites_user_id = users.id OR favorites.favorites_user_id is null
WHERE datetime_id = ? AND id = ?

这部分没有按预期工作。

LEFT JOIN favorites ON venue.venue_id = favorites.favorites_venue_id OR venue.venue_id is null
LEFT JOIN users ON favorites.favorites_user_id = users.id OR favorites.favorites_user_id is null

有什么想法吗？

Answer 1

基于评论

SELECT * FROM venue_datetime
LEFT JOIN venue_calendar ON venue_datetime.calendar = venue_calendar.calendar_id
LEFT JOIN venue_events ON venue_calendar.event = venue_events.event_id
LEFT JOIN venue ON venue_events.venue = venue.venue_id
LEFT JOIN favorites ON venue.venue_id = favorites.favorites_venue_id 
LEFT JOIN users ON favorites.favorites_user_id = users.id 
WHERE datetime_id = ? AND (users.id = ? or users.id is null)

或者

SELECT * FROM venue_datetime
LEFT JOIN venue_calendar ON venue_datetime.calendar = venue_calendar.calendar_id
LEFT JOIN venue_events ON venue_calendar.event = venue_events.event_id
LEFT JOIN venue ON venue_events.venue = venue.venue_id
LEFT JOIN favorites ON venue.venue_id = favorites.favorites_venue_id 
LEFT JOIN users ON favorites.favorites_user_id = users.id 
  AND users.id = ?
WHERE datetime_id = ? 

使用外连接条件时必须考虑空值。因此，要么连接必须处理不返回所有值的表中的 null，要么您必须在连接本身上设置限制标准。