I have two tables: USER and SUBJECTS
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 |
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