Two relationated tables, two foreign keys to the same reference. How do I join the data?

Question

I have the following tables:

users table data:

history table data:

And I want to get the whole history with the corresponding user name in each row, something like this:

| id | seller | client |
| 1  | John   | Peter  |
| 2  | John   | Peter  |
| 3  | Peter  | John   |

I have tried with INNER JOIN but I can't get the result because of course I can't query something like:

SELECT history.id, users.name AS seller, users.name as client

So what is the corresponding query to get that result?

Answer 1

You can join twice:

select h.id, us.name seller, uc.name client
from history h
inner join users us on us.id = h.sellerid
inner join users uc on uc.id = h.clientid