create view `narajwwa_shinsei2`.`v_engi_city2` as select 
`narajwwa_shinsei2`.`t_engi_city`.`ID` AS `ID`,
`narajwwa_shinsei2`.`t_engi_city`.`法人CD` AS `法人CD`,
`narajwwa_shinsei2`.`t_engi_city`.`事業所CD` AS `事業所CD`,
`narajwwa_shinsei2`.`t_engi_city`.`免状番号` AS `免状番号`,
`narajwwa_shinsei2`.`t_engi_city`.`選任日` AS `選任日`,
`narajwwa_shinsei2`.`t_engi_city`.`解任日` AS `解任日`,
`narajwwa_shinsei2`.`t_engi_city`.`選任状態CD` AS `選任状態CD`,
`narajwwa_shinsei2`.`t_engi_kojin`.`主任技術者名` AS `主任技術者名`,
`narajwwa_shinsei2`.`t_engi_kojin`.`交付年月日` AS `交付年月日`,
`narajwwa_shinsei2`.`t_engi_kojin`.`ふりがな` AS `ふりがな`,
`narajwwa_shinsei2`.`t_engi_kojin`.`生年月日` AS `生年月日`,
`narajwwa_shinsei2`.`v_hjlist_city`.`市町村CD` AS `市町村CD`,
`narajwwa_shinsei2`.`v_hjlist_city`.`市町村名` AS `市町村名`,
`narajwwa_shinsei2`.`v_hjlist_city`.`法人名称` AS `法人名称`,
`narajwwa_shinsei2`.`v_hjlist_city`.`事業所名称` AS `事業所名称`,
`narajwwa_shinsei2`.`v_hjlist_city`.`指定番号` AS `指定番号`
 from (`narajwwa_shinsei2`.`t_engi_city` join `narajwwa_shinsei2`.`t_engi_kojin`
 on `narajwwa_shinsei2`.`t_engi_city`.`免状番号` = `narajwwa_shinsei2`.`t_engi_kojin`.`免状番号`)
 join `narajwwa_shinsei2`.`v_hjlist_city`
 on (`narajwwa_shinsei2`.`t_engi_city`.`法人CD` = `narajwwa_shinsei2`.`v_hjlist_city`.`法人CD`
 and `narajwwa_shinsei2`.`t_engi_city`.`事業所CD` = `narajwwa_shinsei2`.`v_hjlist_city`.`事業所CD`
 and `narajwwa_shinsei2`.`t_engi_city`.`登録市町村CD` = `narajwwa_shinsei2`.`v_hjlist_city`.`市町村CD`)
