x69 x69 - 7 months ago 12
SQL Question

MysQL : How to set default value for empty row?

I have 4 tables.

1. Course 2. Semester 3. AssignTeacher 4. Teacher.

Course has id, name, code.
Semester has id, name, course_id.
AssignTeacher has id,course_id,teacher_id.
Teacher has id, name.


Now I want to find courses.code, courses.name, semesters.name and teachers.name
from the above tables. where teachers.name should come with those teachers who has id in AssignTeacher table. Now with the course table when there is no teacher id with that course instead of showing null value can i set some default value like 'None' in the Query?

Here is Query I have written till now though i wasn't getting what i expected:

SELECT courses.code, courses.name, semesters.name,teachers.name
FROM semesters
JOIN courses on courses.semester_id = semesters.id
JOIN assign_teachers on assign_teachers.course_id=courses.id
JOIN teachers on assign_teachers.id=teachers.id

Answer

Of course, you should use COALESCE() which replace null's with wanted value :

SELECT courses.code, courses.name, semesters.name,COALESCE(teachers.name,'None') as teacher_name
from semesters join courses on  courses.semester_id = semesters.id 
  join assign_teachers on assign_teachers.course_id=courses.id
   join teachers on assign_teachers.id=teachers.id

But that seems weird that you get null values, when there is no teacher.id this record should be filtered since you are using normal joins. I think you should use LEFT join instead :

SELECT courses.code, courses.name, semesters.name,COALESCE(teachers.name,'None') as teacher_name
from semesters join courses on  courses.semester_id = semesters.id 
  left join assign_teachers on assign_teachers.course_id=courses.id
   left join teachers on assign_teachers.id=teachers.id