mySQL: Multi-column join on several tables part II

I am adding a 5th table to an existing join. The original query will always return a single row because the where clause specifies a unique ID. Here are the tables we are 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 is the existing query I am using:

SELECT a.*, e.citympg, e.hwympg
FROM table1 a
  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
                and c.modelname = e.model
 开发者_StackOverflow中文版               and a.caryear = e.year
  WHERE a.carid = $carid;

There are 2 issues that I need to solve -

1. When there is no match on table 5, it does not return any results. It would seem that I need to do some sort of left join or split the query and do a union.

2. When there is a match on table 5, it returns multiple rows. Since the criteria that would return a single row is not being used, I would settle for an average of citympg and hwympg.

Can both objectives be achieved with a single query? How?

Assuming I understand what you want correctly... This query will constrain the results from table5 to one row per combination of the join criteria, returning average city/hwy mpg.

SELECT a.*, e.citympg, e.hwympg
FROM table1 a
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) as citympg, 
                  AVG(hwympg) as hwympg           
           FROM table5 
           GROUP BY year, make, model) e on b.makename = e.make
                                         and c.modelname = e.model
                                         and a.caryear = e.year
WHERE a.carid = $carid;

Note that it will return NULL mpg values when no record in table5 exists.

The usual approach is to use correlated subqueries like this:

SELECT a.*
  , (SELECT avg(e.citympg)
     FROM table5 e
     WHERE e.make = b.makename
       AND e.model = c.modelname
       AND e.year = a.caryear
    ) as citympg
  , (SELECT avg(e.hwympg)
     FROM table5 e
     WHERE e.make = b.makename
       AND e.model = c.modelname
       AND e.year = a.caryear
    ) as hwympg
FROM table1 a
  JOIN table2 b on a.makeid=b.makeid
  JOIN table3 c on a.modelid=c.modelid
  JOIN table4 d on a.catid=d.catid
WHERE a.carid = $carid