CODEWITHSUNDEEP
sql
Biggsy
Biggsy

Reputation: 461

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, selfreport.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?

Upvotes: 3

Views: 10311

Answers (3)

Adriano Carneiro
Adriano Carneiro

Reputation: 58595

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.

Upvotes: 2

James Hill
James Hill

Reputation: 61792

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

Upvotes: 3

Johan
Johan

Reputation: 76537

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

Upvotes: 2

Related Questions