当一个字段为null时，左外连接

Question

i have 3 tables t1,t2,t3 and its fields are given below

t1- t1id,name,age;
t2- t2id,t1id,date;
t3- t3id,t2id,time;

My Query is

select concat(t1.name,',',t2.date,',',t3.time) 
from t1 
left outer join t2 on t1.t1id=t2.t1id 
left inner join t3 on t2.t2id=t3.t2id
where t1.age= 12

Some times t2id doesn't present in t3 table at that time i need result as name,date, how is it possible in a single Query?

Answer 1

You could use ifnull() to make concat() work even if some values are null

select concat(t1.name, ',', ifnull(it2.date, ''), ',', ifnull(t3.time,'')) 
from t1 
left outer join t2 on t1.t1id=t2.t1id 
left inner join t3 on t2.t2id=t3.t2id 
where t1.age= 12

Of use CONCAT_WS()

select concat_ws(',', t1.name, it2.date, t3.time) 
from t1 
left outer join t2 on t1.t1id=t2.t1id 
left inner join t3 on t2.t2id=t3.t2id 
where t1.age= 12