I have three tables:
USERS containing data of all users in the database (columns: ID, first name, last name, email)REQUESTS containing various requests by users (columns: ID, applicant)DELEGATES containing user IDs of supervisor and approver for the request (columns: ID, request_id, supervisor, approver)Applicant, supervisor and approver are IDs related to user.id. Request_id is linked to REQUESTS field ID.
How do I create a select and join that would display the email of an applicant, supervisor and approver in the same row? Would using temporary tables help?
Table USERS
| ID | First name | Last name | |
|---|---|---|---|
| 1 | John | Doe | [email protected] |
| 2 | Jane | Doe | [email protected] |
| 3 | Baby | Doe | [email protected] |
Table REQUESTS
| ID | Applicant |
|---|---|
| A | 1 |
| B | 2 |
| C | 3 |
Table DELEGATES
| ID | Request_ID | Supervisor | Approver |
|---|---|---|---|
| x | A | 2 | 3 |
| y | B | 3 | 2 |
| z | C | 1 | 1 |
Current result based on REQUESTS (left join with USERS and DELEGATES)
| ID | Applicant | Supervisor | Approver |
|---|---|---|---|
| A | [email protected] | 2 | 3 |
| B | [email protected] | 3 | 2 |
| C | [email protected] | 1 | 1 |
I selected ID from REQUESTS, mail from USERS and supervisor and approver from REQUESTS with left join of USERS linking REQUESTS applicant with ID in USERS as well as left join of DELEGATES linking REQUESTS ID with delegates table field request id.
When creating the left join between REQUESTS and USERS I get the email for the applicant, but I was not able to define a select that would use REQUESTS column supervisor (which is related to USERS field id) to display the email of that user.
The result I would like to achieve--not only applicant, but also supervisor and approver are listed as email and not ID:
| ID | Applicant | Supervisor | Approver |
|---|---|---|---|
| A | [email protected] | [email protected] | [email protected] |
| B | [email protected] | [email protected] | [email protected] |
| C | [email protected] | [email protected] | [email protected] |
You need multiple joins to the users table, basically once for each relation:
select r.id, u1.email applicant, u2.email supervisor, u3.email approver
from requests r
inner join delegates d on d.request_id = r.id
inner join users u1 on u1.id = r.applicant
inner join users u2 on u2.id = d.supervisor
inner join users u3 on u3.id = d.approver
Note that I used inner joins everywhere rather than left joins, which you mentioned; in a well defined schema, integrity constraints should prevent "orphan" user ids in the referring tables, and avoid the need to left join.