Всем привет,

 возникла задача 
есть 2 таблицы: countries и countries_languages
мне на вход поступает параметр языка например "ru",
 и я должен получить все страны с языком ru
причем если нет языка выводить тот что по умолчанию стоит английский
задача состоит в том что бы решить все это одним запросом

вот как я думал это реализовать http://sqlfiddle.com/#!9/eff6e/1

вот тестовые данные и запрос что делал

CREATE TABLE IF NOT EXISTS `countries` (
`id` int(10) unsigned NOT NULL,
  `iso` varchar(2) NOT NULL,
  `popular` int(11) DEFAULT NULL,
  `status` enum('public','draft') NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=387 ;

CREATE TABLE IF NOT EXISTS `countries_languages` (
`id` int(10) unsigned NOT NULL,
  `country_id` int(11) NOT NULL,
  `language` varchar(2) NOT NULL,
  `title` varchar(72) NOT NULL,
  `meta_keywords` text,
  `meta_description` text,
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00'
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2719 ;

ALTER TABLE `countries`
 ADD PRIMARY KEY (`id`);
ALTER TABLE `countries_languages`
 ADD PRIMARY KEY (`id`);

INSERT INTO `countries` (`id`, `iso`, `popular`, `status`, `created_at`, `updated_at`) VALUES
(1, 'al', NULL, 'public', '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(2, 'ad', NULL, 'public', '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(3, 'ar', NULL, 'public', '0000-00-00 00:00:00', '0000-00-00 00:00:00');

INSERT INTO `countries_languages` (`id`, `country_id`, `language`, `title`, `meta_keywords`, `meta_description`, `created_at`, `updated_at`) VALUES
(1, 1, 'en', 'Albania', NULL, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(2, 2, 'en', 'Andorra', NULL, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(3, 3, 'en', 'Argentina', NULL, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(2257, 1, 'ru', 'Албания', NULL, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00'),
(2259, 3, 'ru', 'Аргентина', NULL, NULL, '0000-00-00 00:00:00', '0000-00-00 00:00:00');

сам запрос
select * from
  (
select * from `countries` 
  left join `countries_languages` as `ml` on `countries`.`id` = `ml`.`country_id` and `ml`.`language` = 'ru'
  ) as t1
  
  join `countries_languages` as `ml` on `t1`.`id` = `ml`.`country_id` and `ml`.`language` = 'en'
этот запрос выдает ошибку "Duplicate column name 'id'"

может кто то знает простое и элегантное решение данной задачи?

Логика где-то верная - один join и один left join. Только намудрено с внутренним запросом. То, что в скобках, SQL пытается объеднить в таблицу, а в ней дублируются колонки, что для таблицы нельзя. Т.е. во внутреннем запросе нельзя изпользовать *. В данном случае там оказываются две колонки id. Из таблицы `countries` и `countries_languages`. Явно перечислите колонки и будет запрос работать.

Может мало понял, что вы хотите, но как понял, запрос проще:

select c.`id`, c.`iso`,
      case when cl.`id` is not null then cl.`language`
           else dcl.`language` end as `language` from `countries` as c
join `countries_languages` as dcl on c.`id` = dcl.`country_id` and dcl.`language` = 'en'
left join `countries_languages` as cl on c.`id` = cl.`country_id` and cl.`language` = 'ru'

Еще, у вас странная структура таблиц. Неправильная. Таблица `countries_languages` должна исходя из названия хранить языки стран. А в ней в колонке title находятся названия стран. А в таблице стран `countries` названий стран нет.


Этот ответ добавлен с нового Винграда - http://vingrad.com

спасибо, 

логика таблиц такая
countries - в ней хранится все то что не требует перевода
countries_languages - в ней хранится все что требует перевода и язык локали, в будущем возможно будут храниться language_id из таблицы languages, вместо локали

languages - в ней хранятся все языки

Переведите ЭТО на русский...

А вообще запрос должен быть типа 

Если, конечно, я верно понимаю, что en имеется для любой страны (вернее, считается, что имеется, даже если запись отсутствует).

Добавлено через 1 минуту и 18 секунд
PS. Давая ссылки на sqlfiddle - проверяйте своё творчество ТАМ.

да, Akina, ваш запрос на этих данных тоже верный, если задача не четко описана ) И он проще. Но он глупый так же как и мой.
Мой запрос - просто шаблон, который можно расширять. Вообще, скорее всего, для будущей задачи надо не 'en', 'ru' выводить, а language_id. Мой запрос не вернет английский для страны, у которой нет английского. Т.е. если будут такие данные. Ваш вернет любой стране 'en', даже если нет записей.


Мой запрос - шаблон, который можно расширять. Но если задача практическая, то:
case when cl.`id` is not null then cl.`language`
           else dcl.`id` end as dcl.`id`
надо поменять и использовать как вьюху или функцию как внутренний подзапрос.


Этот ответ добавлен с нового Винграда - http://vingrad.com

классное решение

спасибо

case when cl.`id` is not null then cl.`language`
           else dcl.`language` end as `language`

Вот так надо ) Может я с  багом отсылал, потом поправил.

На самом деле это стандартное решение, как сделать одну колонку из нескольких таблиц по некоторым условиям

Этот ответ добавлен с нового Винграда - http://vingrad.com