Which JOIN to use for 3 tables

[codeline]

I have 3 tables I'd like to pull data from (user, location_country, location_continent).

 

The location tables are structured pretty basic:

location_continent has a continent_id, continent_name

location_country has a country_id, country_name AND continent_id

 

I want to display my collection of users and within each user, display their country and continent.

 

What would be the ideal JOIN method to use in this situation where the majority of my content is in one table (user) but I want to pull the names of the country and continent from the 2 other tables?

[Psycho]

Would have been nice to see the relevant fields from the user table. I "assume" that the user record only has a foreign key back to the country table.

SELECT u.field1, u.field2, u.field3,
       ctry.country_name
       cont.continent_name
FROM user as u
LEFT JOIN location_country as ctry
  ON u.country_id = ctry.country_id
LEFT JOIN location_continent as cont
  ON ctry.continent_id = cont.continent_id

[codeline]

Sorry, forgot to list the structure of the "user" table, but, you did assume correctly. It just has a field "country_id". However, I also wanted to get opinions if I also included a "continent_id" within the user table as well.

 

And I'm not too familiar with all the MySQL syntax but I'm assuming that the "as u" or "as ctry" is just like setting variables for the table names?

[Psycho]

However, I also wanted to get opinions if I also included a "continent_id" within the user table as well.

If user ahs a foreign key to country and country has a foreign key to continent then you don't need (or want) to link the user and continent directly.

 

... I'm assuming that the "as u" or "as ctry" is just like setting variables for the table names?

Correct, makes queries much more readable.