Join the same table based on a same column-CodePudding

I have two tables users and articles. The modified_by column in the Books table holds the corresponding users id who modified it. The user_id column in the Books table is the author. Both user_id and modified_by of the books table look to the id in the users table.

create table USERS (id int, name varchar(55));
insert into USERS values
( 1, 'person1'),
( 2, 'person2');



create table BOOKS (id int, user_id int, modified_by int);
insert into BOOKS values
(1, 2, 2),
(2, 2, 2),
(3, 2, 2),
(4, 1, 2);

My task is to display the books with name of the user in both the modified_by and user_id columns after joining. So far I have joined and got the name in the user_id column. How can I do the same for the modified_by column as it currently just shows the id and not the name

Current query

SELECT
  books.id,
  books.user_id,
  users.name,
FROM
  books
INNER JOIN users ON
  books.user_id = users.id

Result required:

(1, person2, person2),
(2, person2, person2),
(3, person2, person2),
(4, person1, person2);

CodePudding user response：

You can join the users table twice :

SELECT
  b.id,
  u1.name as `user`,
  u2.name as modified_by
FROM
  BOOKS b 
INNER JOIN USERS u1 ON b.user_id = u1.id
INNER JOIN USERS u2 ON b.modified_by = u2.id;

Try it here : https://dbfiddle.uk/I1KzRNSs

CodePudding user response：

You can use join the users table twice as

SELECT b.id, u1.name AS user_id, u2.name AS modified_by
  FROM books b
  JOIN users u1
    ON b.user_id = u1.id
  JOIN users u2  
    ON b.modified_by = u2.id

Demo

Indeed, it's better to use LEFT OUTER JOIN instead of INNER JOIN if existence of non-matching values among tables is the case

CodePudding user response：

I think what you're looking for would be this. I haven't done SQL in a while so there could be some syntax errors :), but it should be in the ballpark.

SELECT
  b.id,
  u1.name,
  u2.name
FROM
  books as b
INNER JOIN users as u1 ON
  b.user_id = u1.id
INNER JOIN users as u2 ON
  b.modified_by = u2.id