MySQL: Multi-column join on several tables

Question

I have several tables that I am joining that I need to add another table to and I can't seem to get the right query. Here is what I have now -

Table 1
carid, catid, makeid, modelid, caryear

Table 2
makeid, makename

Table 3
modelid, modelname

Table 4
catid, catname

The query I am using to join these is:

SELECT * 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;

Now I need to add a 5th table that I am getting from a 3rd party that I am having a hard time adding to my existing query. The new table has these fields -

Table 5 id, year, make, model, citympg, hwympg

I need the citympg and hwympg based on caryear from table 1, makename from table 2, and modelname from table 3. I know I can do a second query with those values, but I would prefer to do a single query and have all of the data in a single row. Can this be done in a single query? If so, how?

Answer 1

it's possible to have more than condition in a join. does this work?

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
                and a.caryear = e.year
  WHERE a.carid = $carid;

...though your question is not clear. Did you only want to join table5 to the others, or was there something else you wanted to do with table5?