如何在 MySQL 中对同一个表的多个列进行 INNER JOIN

我有以下问题:我有三个表:日志(log)、媒体和用户.

CREATE TABLE `users` (
  `id` INTEGER NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB
ROW_FORMAT=DEFAULT;

CREATE TABLE `media` (
  `id` INTEGER NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB
ROW_FORMAT=DEFAULT;

CREATE TABLE `log` (
  `id` INTEGER NOT NULL AUTO_INCREMENT,
  `action` VARCHAR(20) NOT NULL,
  `action_time` DATETIME NOT NULL,
  `user_id` INTEGER NOT NULL,
  `affected_user_id` INTEGER DEFAULT NULL,
  `media_id` INTEGER DEFAULT NULL,
  `comment` TEXT DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB
ROW_FORMAT=DEFAULT;

The point with the log table is, that every action that is made, is done by a user, but some actions affect a user, and some actions affect a media, but not always. Some actions affect both a user and a media. The columns user_id, affected_user_id are foreign keys to users.id
The column media_id is a foreign key to media.id If there isn't a value for the column, the value is NULL.

我想要做的是对LOG进行SELECT查询，并与其他两个表进行内部联接.

首先，我try 了这个查询，但不知道有什么问题，所以这将是我第一次try 这样做:

SELECT log.action, log.action_time, log.user_id, user_name_table.name AS user_name, 
log.affected_user_id, affected_user_name_table.name AS affected_user_name, 
log.media_id, media.title AS media_title, log.comment 
FROM log 
INNER JOIN users AS user_name_table ON user_name_table.id = log.user_id 
INNER JOIN users AS affected_user_name_table ON affected_user_name_table.id = log.affected_user_id 
INNER JOIN media ON media.id = log.media_id 
WHERE log.user_id=1 OR log.affected_user_id=1

我在网上查找了如何在两列上内部连接同一张表，我真的不知道它是否正确，也许这也是问题所在.我原以为这个查询会返回日志(log)表的所有列，以及与user_id和Affated_user_id关联的用户的名称.

在日志(log)表中，有足够的行具有USER_ID=1或IMPACTED_USER_ID=1.但是不知何故，我的结果集是空的.我没有收到错误消息，所以我的查询语法应该是正确的.

我还try 了其他几个类似的版本:

SELECT log.action, log.action_time, log.user_id, users.name AS user_name, log.affected_user_id, 
log.media_id, media.title AS media_title, log.comment 
FROM log 
INNER JOIN users ON users.id = log.user_id OR users.id=log.affected_user_id
INNER JOIN media ON media.id = log.media_id 
WHERE log.user_id=1 OR log.affected_user_id=1

SELECT log.action, log.action_time, log.user_id, users.name AS user_name, 
log.affected_user_id, 
log.media_id, media.title AS media_title, log.comment 
FROM log 
INNER JOIN users ON users.id = log.user_id = affected_user_id
INNER JOIN media ON media.id = log.media_id 
WHERE log.user_id=1 OR log.affected_user_id=1

我在网上搜索了解决方案，但似乎没有找到.我甚至询问了ChatGPT:)，但它说我的查询完全正确，应该返回我的结果集.