CODEWITHSUNDEEP
mysqlsql
Punkrock760
Punkrock760

Reputation: 51

How do I populate names in two columns using MySQL?

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     |
+------------+---------+----------+

Upvotes: 2

Views: 333

Answers (2)

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270391

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.

Upvotes: 3

Related Questions