Creating new view with match value from another table SQL

Question

I have 2 tables like in the picture. In the first table I have column ID, in the second table I also have ID column and email column. In the end, I want a table 1 with specific email from table 2 which match with the ID column. Can you help me to create a view with this criteria?

Table 1 - Location:

| Location | Region |Senior_ID|Junior_ID|Manager_ID
| 1        | ACB    |xyz_123  |xyz_456  |
| 2        | YJT    |xyz_123  |xyz_456  |xyz_789
| 3        | HUP    |         |xyz_456  |xyz_789

Table2 - User:

| Name     | ID         |Email
| Mark     | xyz_123    |[email protected]
| Toby     | xyz_456    |[email protected]
| Elena    | xyz_789    |[email protected]

Final Table:

| Location | Region |Senior_mail    |Junior_mail     |Manager_mail
| 1        | ACB    |[email protected] |[email protected]  |
| 2        | YJT    |[email protected] |[email protected]  |[email protected]
| 3        | HUP    |               |[email protected]  |[email protected]

I tried with select and case when a.ID = b.ID but it will create new column. I only want to replace the ID in the first column with email.

Answer 1

users should act as separate tables for seniors, juniors and managers.

   select l.location,
     l.region,
     s.email senior_mail,
     j.email junior_mail,
     m.email manager_mail
   from location l left join users s on s.id = l.senior_id
                   left join users j on j.id = l.junior_id
                   left join users m on m.id = l.manager_id
   order by l.location;

  LOCATION REG SENIOR_MAIL     JUNIOR_MAIL     MANAGER_MAIL
---------- --- --------------- --------------- ---------------
         1 ACB [email protected]  [email protected]
         2 YJT [email protected]  [email protected]  [email protected]
         3 HUP                 [email protected]  [email protected]