SQL: connection table displaying a null value

Question

I have a table like this:

table1:

id | item_name | entered_by | modify_by 1 | banana | 2 | 1 2 | apple | 4 | 3 3 | orance | 1 | 1 4 | pineapple | 5 | 3 5 | grape | 6 | 1 

table2:

 id | username 1 | admin 2 | jack 3 | danny 4 | dummy 5 | john 6 | peter 

the query works fine to select whether the values ​​entered_i_i_i_i_i_i__ have a value:

 SELECT t1.id, t1.item_name, t2enteredBy.username enteredBy, t2modifyBy.username modifyBy FROM table1 t1 JOIN table2 t2enteredBy ON t1.entered_by = t2enteredBy.id JOIN table2 t2modifyBy ON t1.modify_by = t2modifyBy.id 

problem : if one of the modifiy_by or given_by fields has a null value, the line is now displayed, I need it to be displayed as "-" if it has a null value, and does not completely hide the line.

SQLFIDDLE HERE

Answer 1

Try the following:

 SELECT t1.id, t1.item_name, COALESCE(t2enteredBy.username, '-') enteredBy, COALESCE(t2modifyBy.username, '-') modifyBy FROM table1 t1 LEFT JOIN table2 t2enteredBy ON t1.entered_by = t2enteredBy.id LEFT JOIN table2 t2modifyBy ON t1.modify_by = t2modifyBy.id 

Fiddle is here .

You need left join to return those rows with null values. Then coalesce will make sure that they are replaced by the given string if they are null .

Answer 2

Try this - use LEFT JOIN instead of JOIN

 SELECT t1.id, t1.item_name,ifnull(t2enteredBy.username,'-') enteredBy, ifnull(t2modifyBy.username,'-') modifyBy FROM table1 t1 LEFT JOIN table2 t2enteredBy ON t1.entered_by = t2enteredBy.id LEFT JOIN table2 t2modifyBy ON t1.modify_by = t2modifyBy.id 

SQL Fiddle Here