|
First of all, unless you need to display records that exist in the model table that don't have associated records in the other tables... you don't need to use a left join.
INNER JOIN and means to only show records common to both tables. Whether the records are common is determined by the fields in join clause.
LEFT JOIN means to show all records from left table (i.e. the one that precedes in SQL statement) regardless of the existance of matching records in the right table.
RIGHT JOIN means opposite of LEFT JOIN, i.e. shows all records from the second (right) table and only matching records from first (left) table.
SELECT model.id from model
INNER JOIN model_in_city ON (model_in_city.model_id = model.id)
INNER JOIN city ON (city.city_id = model_in_city.city_id)
INNER JOIN province on (city.province_id = province.id)
WHERE province.id = [id for bc];
OR
SELECT model.id from model
INNER JOIN model_in_city ON (model_in_city.model_id = model.id)
INNER JOIN city ON (city.city_id = model_in_city.city_id)
INNER JOIN province on (city.province_id = province.id)
WHERE province.name = 'British Columbia,Quebec';
The design of this database is garbage... but that should work
|