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

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 userid开发者_JS百科2 from the users table.

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

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.

something like this,

select m.*,
(select u1.name from users as u1 where m.userid1 = u1.userid) as username1,
(select u2.name from users as u2 where m.userid2 = u2.userid) as username2
from 
maintable as m