I have the following problem: I have three tables: log, media and users.
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.
What I want to do is to make a SELECT query on log, and INNER JOIN with the other two tables.
First I tried this query, not knowing something was wrong, so this would be my first attempt doing something like this:
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
I looked up on the internet how to INNER JOIN the same table on two columns, I don't really know if it's correct, maybe that's also the problem. I expected this query to return me all the columns of the log table and the names of the users associated with user_id and affected_user_id.
In the log table, there a enough rows which have either user_id=1 or affected_user_id=1. But somehow, my result set is empty. I am not getting an error message, so my query syntax should be ok.
I also tried a few other versions like these:
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
I've searched the internet for a solution, but I don't seem to find it. I've even asked ChatGPT :), but it says my query is perfectly ok and should return my result sets.
If I understood you correctly, would this help?
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
AND log.user_id = 1)
OR ( users.id = log.affected_user_id
AND log.affected_user_id = 1)
INNER JOIN media ON media.id = log.media_id
[EDIT], after you posted sample data:
SQL> SELECT * FROM users;
ID NAME
---------- -----
1 Alex
2 Heinz
3 Frank
SQL> SELECT * FROM media;
ID TITLE
---------- ---------------
1 Harry Potter
2 Some other book
3 No idea
SQL> SELECT * FROM log;
ID ACTION ACTION_TIME USER_ID AFFECTED_USER_ID MEDIA_ID
---------- ------------ ------------------- ---------- ---------------- ----------
1 MEDIA_EDIT 2023-08-23 17:56:04 1 5
2 USER_EDIT 2023-08-23 17:56:04 1 3
3 MEDIA_BORROW 2023-08-23 17:56:04 1 3 1
Query, which joins log table to users table twice: once for log.user_id (inner join), another time for log.affected_user_id (outer join).
SQL> SELECT l.action,
2 l.action_time,
3 u.name AS user_name,
4 au.name AS affected_user_name,
5 m.title media_title
6 FROM LOG l
7 JOIN users u ON u.id = l.user_id
8 LEFT JOIN users au ON au.id = l.affected_user_id
9 LEFT JOIN media m ON m.id = l.media_id;
ACTION ACTION_TIME USER_NAME AFFECTED_USER_NAME MEDIA_TITLE
------------ ------------------- --------- ----------------------- ---------------
MEDIA_BORROW 2023-08-23 17:56:04 Alex Frank Harry Potter
USER_EDIT 2023-08-23 17:56:04 Alex Frank
MEDIA_EDIT 2023-08-23 17:56:04 Alex
SQL>