CODEWITHSUNDEEP
mysqlsql
J. Podolski
J. Podolski

Reputation: 211

MySQL left join limit to one row

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.

Upvotes: 2

Views: 3264

Answers (4)

ethan
ethan

Reputation: 975

You're problem goes beyond returning duplicate countries. Basically, with any resource in your list, there's the possibility of duplication. For example, if you have two roads for the city "Los Angeles", than "Los Angeles" will appear twice under the same country.

To solve this problem you can either, 1) have a separate query for each resource you want to return (bad idea), or 2) just handle the duplicate rows in your code that outputs the categories.

Like so:

$categories = array();
foreach ($results as $row) {
    if (!isset($categories[$row->country])) {
        $categories[$row->country] = array(
            'name' => $row->countryName,
            'cities' => array(),
        );        
    }
    if (!isset($categories[$row->country]['cities'][$row->city])) {
        $categories[$row->country]['cities'][$row->city] = array(
            'name' => $row->cityName,
            'roads' => array();
        );  
    }

    // ... and so on, with roads and people
}

The above will populate an array of people grouped by road, road group by city, and city group by country; which you can use to output your data in any way you wish. Of course you could also modify the above code, to output your data immediately, instead of storing it in an array first.

Also, if you only want to return countries that have people, I would do this:

SELECT *
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
   WHERE table4.id IS NOT NULL

Upvotes: 2

timo.rieber
timo.rieber

Reputation: 3867

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

Upvotes: 5

michel-slm
michel-slm

Reputation: 9756

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.

Upvotes: 2

Alexander
Alexander

Reputation: 3179

2) 

SELECT * 
  FROM table1 
 WHERE id IN (
              SELECT id 
                FROM table4)

1) Not possible

Upvotes: 0

Related Questions