Multiple rows as columns in SQLite

sqlite

I have this three tables:

student table

| _id | name      |
| --- | --------- |
| 1   | Student 1 |
| 2   | Student 2 |
| 3   | Student 3 |
| ... | ...       |

subject table

| _id | name      |
| --- | --------- |
| 1   | Subject 1 |
| 2   | Subject 2 |
| ... | ...       |

relationship

| student | subject |
| ------- | ------- |
| 1       | 5       |
| 1       | 4       |
| 2       | 7       |
| 3       | 8       |

What I want is make a query that looks like this:

| student   | subject1  | subject2  |
| --------- | --------- | --------- |
| Student 1 | Subject 5 | Subject 4 |
| Student 2 | Subject 7 | NULL      |
| Student 3 | Subject 8 | NULL      |

Each student has 1 or 2 subjects at most.

Tried with 

SELECT st.name AS student,
       su.name AS subject1,
       su2.name AS subject2
  FROM student AS st
       JOIN
       student_subjects AS ss ON ss.student = st._id
       JOIN
       subject AS su ON ss.subject = su._id
       JOIN
       subject AS su2 ON ss.subject = su2._id
 GROUP BY st._id;

but the result is 

| student   | subject1  | subject2  |
| --------- | --------- | --------- |
| Student 1 | Subject 5 | Subject 5 |
| Student 2 | Subject 7 | Subject 7 |
| Student 3 | Subject 8 | Subject 8 |

Best Answer

Being sure there are a maximum of two subjects makes life a lot easier:

select st.*, su1.*, su2.*
   from student st
   left outer join subject su1
     on su1.id = (select min(sub_id) from rel where stud_id = st.id)
   left outer join subject su2
     on su2.id = (select max(sub_id) from rel where stud_id = st.id)
     and su2.id > su1.id

which gives us:

1|Student 1|4|Subject 4|5|Subject 5
2|Student 2|7|Subject 7||
3|Student 3|8|Subject 8||
Related Question