2

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 |

1 Answer 1

1

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||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.