MYSQL: Joining multiple tables

[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'

[Dido]

You can join as many tables as you want... but, just like MySQL said, it doesn't know the column province.id.. Mainly because you haven't included the table called 'province' in your query I guess.

[k0nr4d]

You are missing the join for province.

[nation-x]

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

[eMonk]

That worked thanks nation-x.

The table model_in_city is picking up multiple results for model.id in British Columbia because they can be shown in multiple cities but only want to display them once in British Columbia.

For example:

Table: model_in_city
column: model_id = 1
column: city_id = 33
column: city_display = 1

column: model_id = 1
column: city_id = 34
column: city_display = 2

Now let's say city_id 33 and 34 are both in British Columbia... how can I just display them just once in British Columbia instead of 2?

[eMonk]

Will I have to create a new column under the table model_in_city named province_id then
run a UNION and SELECT DISTINCT province_id FROM model_in_city ?

[eMonk]

Figured it out, thanks!