Есть три табл.:
1)People(peopleId,name,cityId)
2)City(cityId,countryId,name)
3)Country(countryId,name)
Помогите с выборкой имени человека(people.name) и его страны(country.name), через промежуточную таблицу city.
Вот выборка имени и названия города:

SELECT p1.name AS pn, c1.name AS cn
    FROM people p1 
    INNER JOIN city c1 ON p1.cityid=c2.cityid

Важно: только с использованием JOIN!!!

SELECT p1.name AS pn, c1.name AS cn, country.name as country_name
    FROM people p1 
    INNER JOIN city c1 ON p1.cityid=c2.cityid
    LEFT JOIN country on country.id = city.id_country

SELECT p1.name AS pn, c1.name AS cn, country.name as country_name
    FROM people p1 
    INNER JOIN city c1 ON p1.cityid=c2.cityid
    LEFT JOIN country on country.id = city.id_country

структура хорошая.

дай дамп.

хоть по строк 5 из таблиц

_____________
HTML, CSS (Bootstrap), JS(JQuery, ExtJS), PHP, MySQL, MSSql, Posgres, (TSql, BI OLAP, MDX), Mongo, Git, SVN, CodeIgnater, Symfony, Yii 2, JiRA, Redmine, Bitbucket, Composer, Rabbit MQ, Amazon (SQS, S3, Transcribe), Docker

PEOPLE
peopleid name cityid
76 Kucherenko 10
77 Ostrovin 11

CITY
cityid countryid name
10 1 Rostov
11 2 Madrid
COUNTRY
countryid name
1 Russia
2 Spain

да зачем там дамп. people join city on people.cityid = city.cityid join country on city.countryid = country.countryid. И все.

думаешь?

_____________
HTML, CSS (Bootstrap), JS(JQuery, ExtJS), PHP, MySQL, MSSql, Posgres, (TSql, BI OLAP, MDX), Mongo, Git, SVN, CodeIgnater, Symfony, Yii 2, JiRA, Redmine, Bitbucket, Composer, Rabbit MQ, Amazon (SQS, S3, Transcribe), Docker

SELECT p1.name AS pn, c1.name AS cn, c2.name as countryn    
    FROM people p1     
    INNER JOIN city c1 ON p1.cityid=c2.cityid   
    LEFT JOIN country AS c2 on c1.countryid = c2.countryid

Вот так получилось! :P Всем, кто учавствовал огромное СПАСИБО!!!

вообще не понимаю зачем так делают:

AS pn,  AS cn, AS c2...

помне так куда понятнее:

SELECT people.name, country.name FROM city
INNER JOIN country ON city.countryid = country.countryid
INNER JOIN people ON  city.cityid = people.cityid 

с альясами меньше букв набирать надо)

_____________
HTML, CSS (Bootstrap), JS(JQuery, ExtJS), PHP, MySQL, MSSql, Posgres, (TSql, BI OLAP, MDX), Mongo, Git, SVN, CodeIgnater, Symfony, Yii 2, JiRA, Redmine, Bitbucket, Composer, Rabbit MQ, Amazon (SQS, S3, Transcribe), Docker

То же самое, но короче:

SELECT people.name, country.name FROM city
JOIN country USING (countryid)
JOIN people USING (cityid)