SQL: Matching multiple columns value

I have a database set up so that the username (username) and id (id) are stored in the members table.

I have another table that records reports and I record each column in the table (fid), who reported it (rid) and who they were reporting (id) which both match to the user's id in the members table.

How could I get a query to pull the username for both the rid and id?

My current query is

SELECT selfreport.fid, s开发者_Python百科elfreport.rid, 
       selfreport.id, members.username as username
FROM members, selfreport
WHERE members.id = selfreport.id
ORDER BY fid

but this only gets the username for who they were reporting. How can I get it to pull the username for both?

You need to join to your members table twice. Try something like this:

SELECT      selfreport.fid,
            selfreport.rid,
            selfreport.id,
            COALESCE(WhoReported.username, 'Not Specified') AS WhoReportedUN,
            COALESCE(ReportedTo.username, 'Not Specified') AS ReportedToUN
FROM        selfreport
LEFT JOIN   members WhoReported ON WhoReported.id = selfreport.id
LEFT JOIN   members ReportedTo ON ReportedTo.id = selfreport.rid
ORDER BY    fid

Do not use implicit SQL '89 joins they are an antipattern.
Use explicit join syntax instead.

SELECT s.fid, s.rid, s.id, m1.username as username, m2.username as rusername
FROM selfreport S
INNER JOIN members m1 ON (m1.id = s.id)
INNER JOIN members m2 ON (m2.id = s.rid)
ORDER BY s.fid

If id or rid is optional, use a left join.

SELECT 
  s.fid, s.rid, s.id
  , COALESCE(m1.username, 'nobody') as username
  , COALESCE(m2.username, 'nobody') as rusername
FROM selfreport S
LEFT JOIN members m1 ON (m1.id = s.id)
LEFT JOIN members m2 ON (m2.id = s.rid)
ORDER BY s.fid

You need to join members twice:

SELECT selfreport.fid, 
       selfreport.rid, 
       selfreport.id, 
       m1.username AS ReportToUsername, 
       m2.username AS ReporteeUsername 
FROM   selfreport 
       INNER JOIN members m1 
         ON m1.id = selfreport.id 
       INNER JOIN members m2 
         ON m2.id = selfreport.rid 
ORDER  BY fid 

Since you were doing an implicit join in your original query, I believe INNER JOIN will suit you well. However, if it's possible to have null values in selfreport.id or selfreport.rid, you should use LEFT JOIN instead.