mysql - 使用 MYSQL 跨表选择数据的连接语句出现问题

Question

我正在尝试显示用户创建的事件和他注册的事件。我为此准备了三个表。

//events
| event_id | event_title  | event_details   | event_timestamp | userid
    1           title1        test              1234              1
    2           title2         testing2         123               2


//registration_items : event_id references events.event_id
| id | event_id | task_name 
   1       2        task 1

//registration_signup : id references registration_items.id
| id  | userid | timestamp 
   1       1        1234

这是我当前的查询。 现在它只显示用户创建的事件。它应该显示创建的事件和他注册的事件

select events.*, registration_items.*, registration_signup.*, users.username from events
INNER JOIN users on users.userid = events.userid
LEFT JOIN registration_items ON registration_items.event_id = events.event_id
LEFT JOIN registration_signup ON registration_signup.id = registration_items.id
WHERE events.userid = '$user_id' OR registration_signup.userid = '$user_id' ORDER BY events.event_timestamp DESC

对于 userid1，输出应该是

Title
title1  (the user created this)
title2  (the user signed up for this)

对于 userid2，输出应该是

Title
title2

Answer 1

select events.*, registration_items.*, registration_signup.*, users.username
from events
  INNER JOIN users on users.userid = events.userid
  LEFT JOIN registration_items ON registration_items.event_id = events.event_id
  LEFT JOIN registration_signup ON registration_signup.id = registration_items.id
WHERE registration_signup.userid = '$user_id'

union

select events.*, registration_items.*, registration_signup.*, users.username
from events
  INNER JOIN users on users.userid = events.userid
  INNER JOIN registration_items ON registration_items.event_id = events.event_id
  INNER JOIN registration_signup ON registration_signup.id = registration_items.id
WHERE events.userid = '$user_id'

ORDER BY events.event_timestamp DESC