add left join and restructure