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

use left join instead of join

Suhel Meman
– Suhel Meman

2013-10-31 07:40:09 +00:00
Commented Oct 31, 2013 at 7:40 — Suhel Meman
– Suhel Meman, Commented Oct 31, 2013 at 7:40
Use LEFT JOINs and COALESCE.

geomagas
– geomagas

2013-10-31 07:41:52 +00:00
Commented Oct 31, 2013 at 7:41 — geomagas
– geomagas, Commented Oct 31, 2013 at 7:41

Mosty Mostacho · Accepted Answer · 2013-10-31 07:40:10Z

6

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.

answered Oct 31, 2013 at 7:40

Mosty Mostacho

43.6k16 gold badges99 silver badges124 bronze badges

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

Comments

Suhel Meman · Accepted Answer · 2013-10-31 07:41:38Z

1

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

answered Oct 31, 2013 at 7:41

Suhel Meman

3,8621 gold badge20 silver badges27 bronze badges

6 Comments

Arion Over a year ago

This will not handle null values. The OP was asking for if the values is null then '-'

Suhel Meman Over a year ago

thanks Arion, just update the answer with the fiddle

Arion Over a year ago

I would most probably use COALESCE. Because you do not know it he uses MYSQL

Suhel Meman Over a year ago

he tag the question with Mysql

Arion Over a year ago

Okay sorry did not see that

|

Collectives™ on Stack Overflow

SQL: join table showing null value as well

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related