Need help with LEFT JOIN

[eMonk]

I'm trying to list all the models listed in the province British Columbia. Here's my tables:

 

Table: model
column: id = 1,2

Table: city
column: city_id = 33,34
column: city_name = Vancouver,Victoria
column: province_id = 9,9

Table: model_in_city
column: model_id = 1,2
column: city_id = 33,34

Table: province
column: id = 9,10
column: name = British Columbia,Quebec

 

I believe these are the conditions that are needed:

 

model.id = model_in_city.model_id

city.city_id = model_in_city.city_id

city.province_id = province.id

WHERE province.name = British Columbia

 

I tried the following query but can't seem to LEFT JOIN 2 tables?

 

SELECT id from model
LEFT JOIN model_in_city ON (model_in_city.model_id = model.id)
LEFT JOIN city ON (city.province_id = province.id);

 

MySQL said:

 

#1054 - Unknown column 'province.id' in 'on clause'

[mikosiko]

you are not naming your table province in the select, but just making reference to one of it fields, hence the error.

[awjudd]

As mikosiko said, you need to first reference the table you are trying to access before you can pull them into your query.

 

~awjudd