CODEWITHSUNDEEP
mysqldatabase
arjay0601
arjay0601

Reputation: 475

Set another Value if Not exist in mysql

SELECT subjects.name as name , grades.grade as grade,  concat(instructors.lastname, ", ", instructors.firstname , " ", left(instructors.middlename, 1),".") as instructor
    FROM subjects
    JOIN grades
    JOIN instructors
    WHERE grades.student_id = 1 AND subjects.id = grades.subject_id AND instructors.subject_id = subjects.id

Result is

English    Instructor    Grade
Math       Instructor    Grade

But What I wanted is to give "N/A" in subjects that has no instructors yet. Example

English    Instructor    Grade
Math       Instructor    Grade
Science    N/A           Grade
History    N/A           Grade

How I am going to set a condition in my mySQL so the result will look exactly in the above example?

Thanks In Advance

Upvotes: 0

Views: 122

Answers (3)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

LEFT JOIN instead of INNER JOIN like so:

SELECT 
  s.name as name , 
  g.grade as grade,  
  IFNULL(concat(i.lastname, ", ", i.firstname, ...), 'N/A') as instructor
FROM subjects s
LEFT JOIN grades g ON g.subject_id = s.id 
LEFT JOIN instructors i ON g.subject_id = i.subject_id ;

SQL Fiddle Demo

Note that: The way you write JOINs is not correct. There is no ON clause after the JOIN clause. You have to put the join condition after the ON clause.

Upvotes: 1

Habibillah
Habibillah

Reputation: 28695

You can Left join or right join instead of join or inner join. Left Join will show all data on left side and null on right side if no related data. So your query will become:

SELECT subjects.name as name , grades.grade as grade,  
isnull(concat(instructors.lastname, ", ", instructors.firstname , " ",
left(instructors.middlename, 1),"."), 'N/A') as instructor
FROM subjects
LEFT JOIN grades ON subjects.id = grades.subject_id 
LEFT JOIN instructors  ON instructors.subject_id = subjects.id
WHERE grades.student_id = 1

Upvotes: 0

Sashi Kant
Sashi Kant

Reputation: 13465

TRy this ::

USing a left join and a ifnull

SELECT subjects.name as name , grades.grade as grade,  ifnull(concat(instructors.lastname, ", ", instructors.firstname , " ", left(instructors.middlename, 1),"."),'N/A)' as instructor
    FROM subjects
    LEFT JOIN grades on subjects.id = grades.subject_id
    LEFT JOIN instructors on instructors.subject_id = subjects.id
    WHERE grades.student_id = 1

Upvotes: 0

Related Questions