1 CREATE TABLE IF NOT EXISTS `my_orte` (
2 `loc_id` int(11) NOT NULL,
3 `name` varchar(128) NOT NULL,
4 `vorwahl` varchar(8) NOT NULL,
5 `bundesland` varchar(32) NOT NULL,
6 `level` tinyint(4) NOT NULL,
7 `einwohner` int(11) NOT NULL,
8 UNIQUE KEY `loc_id` (`loc_id`)
9 ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
12 INSERT INTO my_orte (loc_id, name, vorwahl, bundesland, level, einwohner)
13 SELECT t_vorwahl.loc_id, t_name.text_val as name, t_vorwahl.text_val as vorwahl, t_name_bundesland.text_val as bundesland, t_hier.level as level, t_einwohner.int_val as einwohner
14 FROM geodb_textdata as t_vorwahl
15 JOIN geodb_textdata as t_name ON t_vorwahl.loc_id = t_name.loc_id
16 JOIN geodb_hierarchies AS t_hier ON t_vorwahl.loc_id = t_hier.loc_id
17 JOIN geodb_textdata as t_name_bundesland ON t_hier.id_lvl3 = t_name_bundesland.loc_id
18 LEFT JOIN geodb_intdata as t_einwohner ON t_vorwahl.loc_id = t_einwohner.loc_id
19 WHERE t_vorwahl.text_type="500400000"
20 AND t_name.text_type = "500100000"
21 AND t_name_bundesland.text_type = "500100000"
22 AND (t_einwohner.int_type = "600700000" OR t_einwohner.loc_id IS NULL);