Mysql – select distinct -> join 3 tables

join;MySQLpivotunion

Hi i need to mix (more than join) three tables, but the id could be present or not on each table (Please see the example below).

I have spend a few days trying join and union but all time some data is missing!!! can someone give me a fresh idea?

Thanks

data example

Best Answer

If MySQL had implemented FULL joins, this would be fairly simple:

select 
    id, 
    a.price as price_1701, 
    b.price as price_1702, 
    c.price as price_1703
from 
    t_1701 as a
    full join t_1702 as b using (id)
    full join t_1703 as c using (id)
order by
    id ;

Unfortunately it hasn't (and neither the sister implementation MariaDB), so you have to restore using a UNION and LEFT joins:

select 
    id, 
    a.price as price_1701, 
    b.price as price_1702, 
    c.price as price_1703
from 
    ( select id from t_1701
      union
      select id from t_1702
      union
      select id from t_1703
    ) as t
    left join t_1701 as a using (id)
    left join t_1702 as b using (id)
    left join t_1703 as c using (id)
order by
    id ;
Related Question