kikerrobles kikerrobles - 3 months ago 11
MySQL Question

MySQL, Select inside Select Distinct returns more than one value

I'm trying to create some tables with values from main table. Expert in PHP but not in MySQL.
Main table has this columns:

Table Places
ISO
Country
Language
Region2 (is the estate)
Region4 (is ths city council)
ID (id for locality)
Locality


Getting countries was not difficult:

CREATE TABLE countries ( id integer(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
iso varchar(2) NOT NULL, language varchar(2) NOT NULL,
name varchar(50) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO countries ( iso , language, name)
SELECT DISTINCT ISO AS iso, Language as language, Country AS name FROM Places WHERE 1;


Now I have to create States, Councils and Cities, and I've been trying for two days with States with something like this (I've tried with some different codes):

CREATE TABLE states ( id integer(11) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
country_id integer(11) UNSIGNED NOT NULL, country_iso varchar(2) NOT NULL,
name varchar(80) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO states (country_id, country_iso, name)
SELECT DISTINCT
(SELECT countries.id AS country_id from countries WHERE countries.iso = Places.ISO),
ISO as country_iso ,
Region2 AS name FROM Places WHERE 1;


But for country_id this Select returns all country id's.
I just need to get the country id in table Countries where countries.iso matches with ISO in Places.

After this table, States, I have to create Councils, getting values from Places, again with a Select Disctinct, and again trying to get the state id from States and maybe de country id as well.

Please, anybody can get me in the rigth way for nesting this selects?
Thanks.

Answer

Seems you need a join

INSERT INTO states (country_id, country_iso, name) 
SELECT DISTINCT  countries.countries.id , ISO, Places.Region2
from countries 
inner join Placesc on countries.iso = Places.ISO
Comments