SQL query to find values without NULL

Question

I have doubt on my SQL.

Please find the query

select a.id, a.phone, b.list,c.ctitle,l,
       if(last_id!=0 and date(activity)=date(start_date), 
       CONCAT(ag.firstname,' ‘,ag.lastname) ,’')
from table name a 
left join <all other tables>
where a.cid = '100' and a.date between '2013-05-01' and '2015-06-14';

The output is showing properly.currently i am getting firstname and lastname. But for some entries first name and last name empty. I need to modify the query so that i can print the non-null value of the column if(last_id!=0 and date(activity)=date(start_date), CONCAT(ag.firstname,' ‘,ag.lastname) ,’') ie same query excluding the null entries for firstname and last name. I tried IS NOT NULL but not working.

Please help

Add to the where clause `AND ag.firstname IS NOT NULL AND ag.lastname IS NOT NULL` — RiggsFolly, Jun 15 '17 at 00:10

score 4 · Answer 1 · answered Jun 15 '17 at 00:08

4

If I understand correctly, you have a problem with NULLs. One simple solution is to use concat_ws():

   concat_ws(' ', ag.firstname, ag.lastname)

If either name is NULL, then it is just ignored in the concatenation.

answered Jun 15 '17 at 00:08

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 2 · answered Jun 15 '17 at 05:12

0

I guess you can find the answer here :MySQL CONCAT returns NULL if any field contain NULL

also you can use IFNull syntax for this:

CONCAT(IFNull(ag.firstname,''),IFNull(ag.lastname,''))

answered Jun 15 '17 at 05:12

ardalan ebrahimi

11
2

SQL query to find values without NULL

2 Answers2