SQL: join table showing null value as well

Question

i have 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 is work fine to select if the entered_by or modify_by do have 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 entered_by field have null value, the row is now showing out, i need it to show it out as '-' if it has null value rather than hide the row completely.

SQLFIDDLE HERE

Answer 1

Try this out:

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 here.

You need a left join to return those rows with null values. Then the 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