display basic information from tables

Question

Hi,

I have two tables i.e.

Users

uid | firstname  
  1 | John  
  2 | Bob  
  3 | Paul  
  4 | Peter

Calls

cid | assigned_to | caller_id  
 1  |      2      |   1       
 2  |      1      |   3  
 3  |      2      |   4  
 4  |      4      |   2  

assigned_to and caller_id are just the uid in users.

I just want to display the results of each call:

call_id | username(assigned_to) | username(caller_id)

How can I do this in SQL?

Thanks,

Answer 1

Try this:

select 
  cid as call_id,
  A.username, -- assingned to
  B.username  -- caller id
from calls
  left join users A on calls.assigned_to = A.uid
  left join users B on calls.caller_id = B.uid