MySQL left join limit to one row

Question

I have the following database structure

table_countries
----------
country_id
country_name

table_cities
----------
city_id
country_id
city_name

table_streets
----------
street_id
city_id
street_name

table_people
----------
person_id
street_id
person_name

There multiple countries, which can have multiple cities, which in turn multiple streets and so on.

I am looking to perform a query that will get a list of all countries that have 1 or more people in them.

The problem is countries table is not linked directly to people table. And LEFT JOIN returns multiple rows for the same country.

Answer 1

For the expected result mentioned in your edit I would change the left joins to inner joins and select only country name with a group by clause. Note the foreign key names in the on clauses, I think you have to clarify/correct your table structures:

SELECT 
    table1.country

FROM
   table1 JOIN table2 ON table1.id = table2.table1_id
   JOIN table3 ON table2.id = table3.table2_id
   JOIN table4 ON table3.id = table4.table3_id

GROUP BY
    table1.country

Answer 2

SELECT * FROM table1 WHERE id IN (
SELECT DISTINCT table1.id
  FROM table1
  LEFT JOIN table2
         ON table1.id = table2.id
  LEFT JOIN table3
         ON table2.id = table3.id
  LEFT JOIN table4
         ON table3.id = table4.id
);

ought to do the trick then? You don't even need DISTINCT, but it will make the inner query sufficient if you just want to get the country IDs.