eMonk

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

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?

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.

__________________
Dido

ADAMO Advertising - Your ULTIMATE traffic partner!

If you need traffic or have traffic, we'd love to help you make the best out of it!

ICQ:24209500 - Skype:diederikvanschaik

k0nr4d

You are missing the join for province.

__________________
Mechanical Bunny Media
Mechbunny Tube Script | Mechbunny Webcam Aggregator Script | Custom Web Development

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:

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!