MySQL JOIN - Don't want NULL Value -


lets have 2 tables:

 table1    table1_id    table1_name   table2    table2_id    table2_name    table2_description    table1_id 

i join so:

 select * table1 left join table2 on table1.table1_id = table2.table1_id 

how can have table1_id return default value instead of null when there no matches in table2?

answer: since table1_id exists in both tables, needed used aliases.

 select *, table1.table1_id tid, table2.table1_id t2id table1 left join table2 on table1.table1_id = table2.table1_id 

if understand correctly, bring in default row , use logic in select:

select t1.*,        coalesce(t2.table2_name, t2def.table2_name) table2_name,        . . . table1 t1 left join      table2 t2      on t1.table1_id = t2.table1_id left join      table2 t2def      on t2def.table1_id = $defaultid; 

Comments