How do I populate names in two columns using MySQL?

Question

Summary

I have 2 tables on my database. The first table contains the web page name and 2 columns containing the User ID of people who created and last modified those pages. The 2nd table contains the names of the people.

How do I populate those names in table one? I did refer to a similar question but this didn't work.

Table One (Page Information):

+------------+---------+----------+
| Page Title | Creator | Modifier |
+------------+---------+----------+
| Page 1     |       1 |        2 |
| Page 2     |       1 |        2 |
| Page 3     |       2 |        2 |
| Page 4     |       2 |        1 |
| Page 5     |       2 |        1 |
+------------+---------+----------+

Table Two (User Table):

+---------+-------+
| User ID | Name  |
+---------+-------+
|       1 | Mark  |
|       2 | Kevin |
+---------+-------+

What I'm trying to do:

+------------+---------+----------+
| Page Title | Creator | Modifier |
+------------+---------+----------+
| Page 1     | Mark    | Kevin    |
| Page 2     | Mark    | Kevin    |
| Page 3     | Kevin   | Kevin    |
| Page 4     | Kevin   | Mark     |
| Page 5     | Kevin   | Mark     |
+------------+---------+----------+

https://stackoverflow.com/questions/5727827/update-one-mysql-table-with-values-from-another — SURU, Feb 05 '20 at 11:18

score 3 · Accepted Answer · answered Feb 05 '20 at 11:26

3

You simply need to JOIN twice:

select p.*, uc.name as creator, um.name as modifier
from pages p join
     users uc
     on p.Creator = uc.UserID join
     users um
     on p.Modifier = um.UserID;

You should use a left join if it is possible for the Creator/Modifier values to be NULL.

answered Feb 05 '20 at 11:26

Gordon Linoff

1,242,037
58
646
786

@Punkrock760 . . . This was also the first correct answer. As the OP, however, you can accept whichever answer you prefer. – Gordon Linoff Feb 05 '20 at 12:07

Jaydip Jadhav · Answer 2 · 2020-02-05T11:38:36.567

2

Try something like this

SELECT t1.PageTitle,t2.Name,t3.Name
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.Creator = t2.UserID
LEFT JOIN Table2 t3 ON t1.Modifier = t3.UserID

edited Feb 05 '20 at 11:38

answered Feb 05 '20 at 11:19

Jaydip Jadhav

12,179
6
24
40

The `MAX` is useless – Slava Rozhnev Feb 05 '20 at 11:29
1

So simple. Thanks so much for this. I took the max out but it worked. – Punkrock760 Feb 05 '20 at 11:36

How do I populate names in two columns using MySQL?

2 Answers2