I'm currently requested to make a query that will be get name of fields from other tables if they're not null!
Let's say w've tables like that:
Student
| id | name | age |
|---|---|---|
| 1 | Mehdi | 22 |
| 1 | Amina | 26 |
Math
| id | section |
|---|---|
| 1 | Algebra |
| 2 | Analysis |
| 3 | Geometry |
English
| id | section |
|---|---|
| 1 | Grammar |
| 2 | Speaking |
| 3 | Vocabulary |
Exam
| idStudent | idMath | idEnglish |
|---|---|---|
| 1 | 1 | Null |
| 1 | Null | 2 |
| 2 | 1 | Null |
We want to query make this expected result:
Exam
| idStudent | Subject |
|---|---|
| 1 | Algebra |
| 1 | Speaking |
| 2 | Algebra |
I've tried so far:
select idStudent,m.section Subject
from Exam exam, Student s, Math m, English eng
where s.id = exam.idStudent
and m.id=exam.idMath
and exam.idEnglish is null
union
select idStudent,eng.section Subject
from Exam exam, Student s, Math m, English eng
where s.id = exam.idStudent
and eng.id=exam.idEnglish
and exam.idMath is null
Is there a better way to do that!
Thanks in advance for your help.
CodePudding user response：
The database model is far from ideal since it uses one table per course; that means that every time a course is added, then a new table would need to be created. A typical model would include a single table for all courses.
Anyway, in your case you can do:
select e.idstudent, c.section from exam e join math c on c.id = e.idmath
union all
select e.idstudent, c.section from exam e join english c on c.id = e.idenglish
Also, please use modern join syntax instead of the 1980s join syntax, since it's much easier to read and the debug.