Multiple rows as columns in SQLite
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 &gt; 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||