mysql join

[bilibytes]

hi all,

i am trying to get all the names of the restaurants in a city.

i have 3 tables:

1. countries -> list of countries in 4 languages (each country has the same id in the different languages)
id | country_id | name | lang

2. cities-> list of all cities in 4 languages (each city is associated with the country_id and has the same id for the different languages)
id | country_id | city_id | name | lang

3. restaurants-> list of all restaurants in the different countries (each restaurant has associated the country_id and the city_id)
id | country_id | city_id | name



ok what i want is to get all the names of the restaurants in a city
i did this:

i pass these two variables $country and $city

"SELECT co.country_id, ci.city_id, p.name
        FROM countries co, cities ci, premises p
        WHERE co.name = '$country' AND
              ci.name = '$city' AND 
              ci.country_id = co.country_id AND 
              p.city_id = ci.city_id";

is this the right way to perform this?
i have to use all these ids because of the languages

should i use a subquery instead?

thankyou very much

[Atli]

Hi.

What is the difference between the "id" and "country_id" field in the "country" table?
On the same note, what is the difference between "id" and "city_id" in the "City" table?
Why do you store both the "country_id" and "city_id" in the "restaurants" table?

If I needed to store a list of countries, and the names of those countries in multiple languages, I would use a structure like this:

Country
-----------------
CountryID Int PK
CountryName VarChar(255) /* In English, as the default name */
-----------------
 
Language
-----------------
LanguageID Int PK
LanguageName VarChar(255) /* In English */
LanguageLocale VarChar(255) /* The name of the language in that language */
-----------------
 
CountryName
-----------------
NameID Int PK
CountryID_FK Int References Country(CountryID)
LanguageID_FK Int References Language(LanguageID)
NameValue VarChar(255)
------------------
 

Which would also work for the Cities and Restaurants, although you could reuse the "Language" table in those as well.

[bilibytes]

Hi.

What is the difference between the "id" and "country_id" field in the "country" table?
On the same note, what is the difference between "id" and "city_id" in the "City" table?
Why do you store both the "country_id" and "city_id" in the "restaurants" table?

If I needed to store a list of countries, and the names of those countries in multiple languages, I would use a structure like this:

Country
-----------------
CountryID Int PK
CountryName VarChar(255) /* In English, as the default name */
-----------------
 
Language
-----------------
LanguageID Int PK
LanguageName VarChar(255) /* In English */
LanguageLocale VarChar(255) /* The name of the language in that language */
-----------------
 
CountryName
-----------------
NameID Int PK
CountryID_FK Int References Country(CountryID)
LanguageID_FK Int References Language(LanguageID)
NameValue VarChar(255)
------------------
 

Which would also work for the Cities and Restaurants, although you could reuse the "Language" table in those as well.

ok, i'll trust in you. as i dont know what is resources costless.

you told me: why do u have a repeated id: id and country_id.

If i understood well, mysql always needs an key/index which should be a non repeated value, and as i have multiple languages i need an id to associate the countries in turkish to the countries i understand -> in english... that is why country_id would be a repeated value.
As i need an index which is non-repeated value, i added id which is key auto increment.

your table structure doesnt need this repetition, that's why i think yours should be better.

So thankyou very much!!

by the way, isn't there a web page where i can get all countries almost ready to be inserted to my table?