MySQL同表两次左连接需求：将请求表ID转换为用户名

To get the view you want—where Updatedby and Createdby show usernames instead of IDs—you’ll need to join the requests table with the users table twice (once for each user ID field) using table aliases to avoid confusion. Here’s the SQL to create the view:

CREATE VIEW request_details AS
SELECT 
    r.ID,
    u_updated.Name AS Updatedby,
    u_created.Name AS Createdby,
    r.DateCreated,
    r.DateUpdated
FROM requests r
INNER JOIN users u_updated ON r.Updatedby = u_updated.ID
INNER JOIN users u_created ON r.Createdby = u_created.ID;

Quick breakdown of how this works: #

• View creation: CREATE VIEW request_details AS sets up a reusable view (feel free to rename request_details to something more fitting for your workflow).
• Table aliases: r shortens requests, u_updated refers to the user who updated the request, and u_created refers to the user who created it. This lets us join the same users table twice without column name conflicts.
• Joins: INNER JOIN ensures we only include requests where both the update and create user IDs exist in the users table. If you might have requests with missing user IDs (and want to show NULL instead of excluding those rows), swap INNER JOIN with LEFT JOIN.
• Column mapping: We pull the Name from each joined user record and label them as Updatedby and Createdby to match your desired output.

Once you run this, querying SELECT * FROM request_details; will return exactly the format you’re after:

Content of the question originates from Stack Exchange, question author John Pham.