MysQL : How to set default value for empty row?

Question

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

x69 - can you show a sample output? Always explain what you want in points. — MontyPython
– MontyPython, Commented May 8, 2016 at 9:36

sagi · Accepted Answer · 2016-05-08 10:02:52Z

1

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

edited May 8, 2016 at 10:02

answered May 8, 2016 at 9:37

sagi

40.6k7 gold badges66 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Hola Over a year ago

The fact is I'm getting column name as COALESCE(teachers.name,'None') and Im not getting all the course list instead of Im getting only those course who has id in assign_teacher table.

sagi Over a year ago

That's why I've added an explanation, telling you that you should use LEFT JOIN, like my second example. About the column name, its only an alias, I've edited the code. Take the second query. @x69

Hola Over a year ago

I have run your second Query as well, same output I'm getting!

Hola Over a year ago

Thanks Bro..Perfect Answer!

splash58 · Accepted Answer · 2016-05-08 09:37:23Z

1

if i understood correctly, change in select list to

COALESCE(teachers.name, 'None')

answered May 8, 2016 at 9:37

splash58

26.2k3 gold badges25 silver badges35 bronze badges

Collectives™ on Stack Overflow

MysQL : How to set default value for empty row?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related