customer
+---------------------------------------------------------------------------------------------+
| id    | email             | firstname     | lastname      | no_pub    | customer_address    |
|---------------------------------------------------------------------------------------------|
| 1     | martin@domain.com | Martin        | Scorcese      | 0         | 4                   |
| 2     | robert@domain.com | Robert        | De Niro       | 0         | 7                   |
| 3     | bruce@domain;com  | Bruce         | Willis        | 0         | 10                  |
+---------------------------------------------------------------------------------------------+

address
+------------------------------------------+
| id    | city             | zipcode       |
|------------------------------------------|
| 4     | Paris            | 75001         |
| 7     | Marseille        | 13000         |
| 10    | Bordeaux         | 33000         |
+------------------------------------------+

sf_geo_cities
+------------------------------------------+
| id    | region_id    | zipcode           |
|------------------------------------------|
| 1     | 1            | 75001             |
| 2     | 2            | 13000             |
| 2     | 3            | 33000             |
+------------------------------------------+

sf_geo_regions
+------------------------------------------+
| id    | name             | zipcode       |
|------------------------------------------|
| 1     | Ile-de-France    | 75001         |
| 2     | Cote d'Azur      | 13000         |
| 2     | Gironde          | 33000         |
+------------------------------------------+

+---------------------------------------------------------------------------------------------+
| id    | email             | firstname     | lastname      | city          | region          |
|---------------------------------------------------------------------------------------------|
| 1     | martin@domain.com | Martin        | Scorcese      | Paris         | Ile-de-France   |
| 2     | robert@domain.com | Robert        | De Niro       | Marseille     | Cote d'Azur     |
| 3     | bruce@domain;com  | Bruce         | Willis        | NULL          | NULL            |
+---------------------------------------------------------------------------------------------+

SELECT c.id, c.email, c.firstname, c.lastname, gc.name, gr.name
FROM customer c
LEFT JOIN address ad ON ad.id = c.customer_address
JOIN sf_geo_cities gc ON gc.zipcode = ad.zipcode
JOIN sf_geo_regions gr ON gr.id = gc.region_id
WHERE no_pub = 0