mySQL: Multi-column join on several tables part II -

i adding 5th table existing join. original query return single row because clause specifies unique id. here tables using:

table 1
carid, catid, makeid, modelid, caryear

table 2
makeid, makename

table 3
modelid, modelname

table 4
catid, catname

table 5 id, caryear, makename, modelname

here existing query using:

select a.*, e.citympg, e.hwympg table1   join table2 b on a.makeid=b.makeid   join table3 c on a.modelid=c.modelid   join table4 d on a.catid=d.catid   join table5 e on b.makename = e.make                 , c.modelname = e.model                 , a.caryear = e.year   a.carid = $carid; 

there 2 issues need solve -

1. when there no match on table 5, not return results. seem need sort of left join or split query , union.

2. when there match on table 5, returns multiple rows. since criteria return single row not being used, settle average of citympg , hwympg.

can both objectives achieved single query? how?

assuming understand want correctly... query constrain results table5 1 row per combination of join criteria, returning average city/hwy mpg.

select a.*, e.citympg, e.hwympg table1 join table2 b on a.makeid=b.makeid join table3 c on a.modelid=c.modelid join table4 d on a.catid=d.catid left join (select year, make, model,                    avg(citympg) citympg,                    avg(hwympg) hwympg                       table5             group year, make, model) e on b.makename = e.make                                          , c.modelname = e.model                                          , a.caryear = e.year a.carid = $carid; 

note return null mpg values when no record in table5 exists.