CODEWITHSUNDEEP
phpmysqlsql
Hola
Hola

Reputation: 2233

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

Upvotes: 2

Views: 654

Answers (2)

sagi
sagi

Reputation: 40471

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

Upvotes: 1

splash58
splash58

Reputation: 26143

if i understood correctly, change in select list to 

COALESCE(teachers.name, 'None')

Upvotes: 1

Related Questions