Search
Search

MySQL left join limit to one row

J. Podolski Published at Dev
14
J. Podolski

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.

timo.rieber

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

MySQL left join limit to one row

From Dev

Mysql Left join with One row

From Dev

MySQL LEFT JOIN with DISTINCT or LIMIT. Need to LEFT JOIN only one value

From Dev

Left Join Subselect with LIMIT in MySQL

From Dev

MySQL Limit and Order Left Join

From Dev

MySQL left join first and second results and return as one row

From Dev

MySQL LEFT JOIN only one row, ordered by column without subquery

From Dev

MySQL LEFT JOIN only one row, ordered by column without subquery

From Dev

Limit Row Display on Left Join with Where Clause

From Dev

MySQL, limit one table in join

From Dev

MySQL Query with LEFT JOIN (SELECT) ORDER and LIMIT

From Dev

mysql using limit in a left join not working properly

From Dev

MYSQL SELECT WITH LEFT JOIN LIMIT 1

From Dev

MYSQL query and LIMIT to one row

From Dev

MYSQL query and LIMIT to one row

From Dev

Left join two values in one row

From Dev

LEFT OUTER JOIN selects just one row

From Dev

Obtaining last occurance of row on MySQL LEFT JOIN

From Dev

MySQL Left Join - Not empty or first row

From Dev

MySQL LEFT JOIN is returning just one row with a where condition on the right table

From Dev

MySql LEFT JOIN returns only one NULL row from the other table

From Dev

SQL LEFT-JOIN with 2 foreign keys and merge them into one row (MySQL)

From Dev

left join tables, use only one row from left table

From Dev

MySQL DISTINCT on one column and LEFT JOIN

From Dev

LEFT OUTER JOIN with LIMIT

From Dev

How to LIMIT a LEFT JOIN

From Dev

How to LIMIT a LEFT JOIN

From Dev

MySQL left join with LIMIT 1 not returning desired result

From Dev

One to one left join

Related Related

  1. 1

    MySQL left join limit to one row

  2. 2

    Mysql Left join with One row

  3. 3

    MySQL LEFT JOIN with DISTINCT or LIMIT. Need to LEFT JOIN only one value

  4. 4

    Left Join Subselect with LIMIT in MySQL

  5. 5

    MySQL Limit and Order Left Join

  6. 6

    MySQL left join first and second results and return as one row

  7. 7

    MySQL LEFT JOIN only one row, ordered by column without subquery

  8. 8

    MySQL LEFT JOIN only one row, ordered by column without subquery

  9. 9

    Limit Row Display on Left Join with Where Clause

  10. 10

    MySQL, limit one table in join

  11. 11

    MySQL Query with LEFT JOIN (SELECT) ORDER and LIMIT

  12. 12

    mysql using limit in a left join not working properly

  13. 13

    MYSQL SELECT WITH LEFT JOIN LIMIT 1

  14. 14

    MYSQL query and LIMIT to one row

  15. 15

    MYSQL query and LIMIT to one row

  16. 16

    Left join two values in one row

  17. 17

    LEFT OUTER JOIN selects just one row

  18. 18

    Obtaining last occurance of row on MySQL LEFT JOIN

  19. 19

    MySQL Left Join - Not empty or first row

  20. 20

    MySQL LEFT JOIN is returning just one row with a where condition on the right table

  21. 21

    MySql LEFT JOIN returns only one NULL row from the other table

  22. 22

    SQL LEFT-JOIN with 2 foreign keys and merge them into one row (MySQL)

  23. 23

    left join tables, use only one row from left table

  24. 24

    MySQL DISTINCT on one column and LEFT JOIN

  25. 25

    LEFT OUTER JOIN with LIMIT

  26. 26

    How to LIMIT a LEFT JOIN

  27. 27

    How to LIMIT a LEFT JOIN

  28. 28

    MySQL left join with LIMIT 1 not returning desired result

  29. 29

    One to one left join

HotTag

Archive