How to select two additional columns from another table based on values in the main table?

Question

I have a table named maintable with 3 columns: id, userid1 and userid2.

Another table named users is keyed by userid, and has name as a column.

I want to select something along the lines of:

SELECT maintable.*, users.name AS username1, users.name AS username2 
FROM maintable, users 
WHERE ...

Basically I want to get all the columns in the maintable row, and add two columns at the end that will draw the names for userid1 and userid2 from the users table.

I'm unsure how to format the where clause for a query like this.

Answer 1

You need to join twice with users:

SELECT m.*, u1.name, u2.name
FROM maintable m 
INNER JOIN users u1 ON (m.userid1 = u1.userid)
INNER JOIN users u2 ON (m.userid2 = u2.userid)

You can read the documentation about MySQL JOIN Syntax here.