MySQL Forums
Forum List  »  Newbie

Re: Sakila giving some strange results
Posted by: Saleem Hasan
Date: April 07, 2015 01:08AM

I am getting this sinking feeling that I am not using the inner joins correctly. :))

When I changed query 4 and wrote it as where country = 'canada'; instead of where city = 'london';

I got a list of cities in Canada and london was not one of them


select city.city_id, city, address.distric, country from city inner join address on city.city_id = address.city_id inner joing country on country.country_id = city.country_id where country = 'canada';

179 Gaineau Quebec Canada
196 Halifax Nova Scotia Canada
300 Lethbridge Alberta Canada
300 Lethbridge Alberta Canada
383 Oshawa Ontario Canada
430 Richmond Hill Ontario Canada
565 Vancouver British Columbia Canada

There is no city_id 313 for London Ontario in Canada

BUT, if I run one inner join
select city_id, city from city inner join country on country.country_id = city.country_id where country = 'canada';
I get all of the cities including 313 which is London in Canada

It turns out that in the address table, there is no city_id = 313 which is London, Ontario. In the address table both records for London (England and Ontario) have city_id 312 and that is why the address table does not show anything for canada.

I am still a bit confused but may be I am using the inner joins correctly :))

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.