INNER JOIN a bunch of two tables or you need a kick to the right side

Question

MySQL

I don’t understand why the expression does not work.
There are two tables:

CREATE TABLE `country` (
  `id` smallint(4) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `code` varchar(3) NOT NULL,
  `currency_code` varchar(3) NOT NULL,
  UNIQUE KEY `id` (`id`,`name`,`code`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

CREATE TABLE `currency` (
  `code` varchar(3) NOT NULL,
  `name` varchar(50) NOT NULL,
  `symbol` char(1) NOT NULL,
  `bank_code` int(10) unsigned NOT NULL,
  UNIQUE KEY `code` (`code`,`name`,`symbol`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SELECT *
FROM `country`
INNER JOIN `currency` ON `currency`.`code` = `country`.`currency_code`
GROUP BY `country`.`currency_code`

I know the country code RU (for example) we have it in the table. country , now I want to know what bank_code the currency has in each of the countries available in the country .

Answer 1

`currency_code` varchar(3)It is better to write down the ID and make the field type INT
UPD:

--
-- Структура таблицы `country`
--

CREATE TABLE IF NOT EXISTS `country` (
  `id` smallint(4) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `code` int(11) unsigned NOT NULL,
  `currency_code` varchar(3) NOT NULL,
  UNIQUE KEY `id` (`id`,`name`,`code`),
  KEY `code` (`code`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

-- --------------------------------------------------------

--
-- Структура таблицы `currency`
--

CREATE TABLE IF NOT EXISTS `currency` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `code` varchar(3) NOT NULL,
  `name` varchar(50) NOT NULL,
  `symbol` char(1) NOT NULL,
  `bank_code` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`,`name`,`symbol`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Request

SELECT *
FROM
  `currency`
  INNER JOIN `country` ON (`currency`.`id` = `country`.`code`)

Answer 2

and why did you decide that it does not work? what exactly is the problem?