CODEWITHSUNDEEP
sql
Emelia Johansson
Emelia Johansson

Reputation: 11

Find user name for multiple user id columns

I am working with an existing database structure (I'm aware the structure might not be perfect, but it is out of my control). I have one table called Users with user id and name among other irrelevant columns.

My problem is with combining users and the table called settlements. The columns in settlements are as following: Case_ID, created_date, approve_date, approving_user_id, control_date, controlling_user_id

How do I interact between the two tables so that my end result shows the names of the ones approving/controlling and not the ID.

I have tried to work with joins but I don't know how to proceed when there are two different users that have approved and controlled. 

select 
u.user_id, u.agent_name, s.creating_date, s.approving_date, 
s.approving_user, s.controlling_date, s.controlling_user 

from USERS u

left join SETTLEMENTS s on u.user.id = s.approving_user

I know that I could join them in too two different tables, one called approve and one control but I would prefer to have the end result in the same table.

Upvotes: 0

Views: 899

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

You want two joins, but you should start with settlements and then bring in the two users tables:

select s.approving_user, s.controlling_date, s.controlling_user,
       s.creating_date, s.approving_date, 
       ua.user_id as approving_user_id, ua.agent_name as approving_agent_name, 
       uc.user_id as controlling_user_id, uc.agent_name as controlling_agent_name
 from settlements s left join
     users ua
     on ua.user.id = s.approving_user left join
     users uc
     on uc.user_id = s.controlling_user_id

Upvotes: 3

Related Questions