Get multiple values from another table by different relations

Question

I have two tables: USER and SUBJECTS

USER table

SUBJECT table

SUBJECT1, SUBJECT2, SUBJECT3 from table USER are foreign keys to column ID in table SUBJECT.

I'm trying to write an SQL query that returns all columns from the first table with the referenced values by the foreign keys to show that one user (teacher) can teach three subjects at a time. I want in the result to get the values from the second table, like this:

| ID | NAME  | AGE | ADDRESS | SUBJECT1 | SUBJECT2 | SUBJECT3 |
+----+-------+-----+---------+----------+----------+----------+
| 1  | John  | 30  | London  | Math     | English  | Sports   |
| 2  | Marry | 40  | London  | English  | Sports   | Biology  |
| 3  | Tom   | 35  | Paris   | English  | Sports   | Russian  |

Answer 1

Try following;)

select
    u.ID, u.NAME, u.AGE, u.ADDRESS, s1.NAME as SUBJECT1, s2.NAME as SUBJECT2, s3.NAME as SUBJECT3 
from USER u
left join SUBJECT s1 on u.SUBJECT1 = s1.ID
left join SUBJECT s2 on u.SUBJECT2 = s2.ID
left join SUBJECT s3 on u.SUBJECT3 = s3.ID