mysql - listing corresponding data of two tables -

i'm pretty new sql , have problem coundn't describe google

select name state  state  id in  (   select state_id    city    id=(select city_id zipcode) ) ; 

this lists states of zipcodes have in database, want list zipcodes (zipcode.zipcode) in additional column corresponding state

thank in advance help

my tables this:

city  +----+----------+-----------+---------------------+---------+---------+ | id | state_id | county_id | name                | lat     | lng     | +----+----------+-----------+---------------------+---------+---------+ |  1 |        1 |         1 | prem, oberbayern    | 47.6833 |    10.8 | |  2 |        2 |         2 | pfullendorf (baden) | 47.9249 | 9.25718 | |  3 |        3 |         3 | wissen, sieg        | 50.7833 |    7.75 | |  4 |        1 |         4 | miltenberg          | 49.7039 | 9.26444 | |  5 |        1 |         5 | moosthenning        | 48.6833 |    12.5 | |  6 |        1 |         1 | bernbeuren          | 47.7333 | 10.7833 | |  7 |        4 |         6 | demmin, hansestadt  |    53.9 | 13.0333 | |  8 |        2 |         7 | konstanz, universit | 47.6667 | 9.18333 | |  9 |        5 |         8 | teutschenthal       |   51.45 |    11.8 | | 10 |        6 |         9 | vierlinden          |  52.515 | 14.3141 | +----+----------+-----------+---------------------+---------+---------+    zipcode  +----+---------+-------------+---------+ | id | city_id | district_id | zipcode | +----+---------+-------------+---------+ |  1 |       1 |        null | 86984   | |  2 |       2 |        null | 88630   | |  3 |       3 |        null | 57537   | |  4 |       4 |        null | 63897   | |  5 |    4312 |         502 | 84164   | |  6 |       6 |        null | 86975   | |  7 |       7 |         778 | 17109   | |  8 |       8 |        null | 78462   | |  9 |       8 |        null | 78464   | | 10 |       8 |        null | 78465   | +----+---------+-------------+---------+    state  +----+------------------------+ | id | name                   | +----+------------------------+ |  1 | bayern                 | |  2 | baden-w?rttemberg      | |  3 | rheinland-pfalz        | |  4 | mecklenburg-vorpommern | |  5 | sachsen-anhalt         | |  6 | brandenburg            | |  7 | niedersachsen          | |  8 | schleswig-holstein     | |  9 | nordrhein-westfalen    | | 10 | th?ringen              | +----+------------------------+ 

use explicit joins:

select z.*, s.name state zipcode z join      city c      on z.city_id = c.id join      state s      on c.state_id = s.id; 

if learning sql, focus on learning join rather in.