Inner join query but checking for NULL values

[Azsen]

MySQL 5.1.41

PHP 5.3

 

Ok I've got a table which stores all the data about a user's location (location_id, address, suburb, city_id, region_id, country_id). The city, region and country data are stored in separate tables. Here's my current query:

 

SELECT city_name, region_name, country_name
FROM locations
INNER JOIN countries ON locations.country_id = countries.country_id
INNER JOIN regions ON locations.region_id = regions.region_id
INNER JOIN cities ON locations.city_id = cities.city_id
WHERE locations.location_id = 87

Which returns:

Dunedin | Otago | New Zealand

Problem is on some rows of the locations table you will get NULL values in the id columns as the user maybe selected a country, but not a region or city. Now if I run that query again with a location row that has a NULL city_id, region_id or country_id then the query fails and returns no data. I need a simple way to check that if the particular id is NULL and then not get the city/region/country name for that id but get the other non null values.

 

I managed to accomplish this in PHP, but there must be a better way to do it in SQL. I don't think adding IS NOT NULL at the end of the where statement works.

$resultB = $db->select("select * from locations where location_id='$locationId'");

foreach ($resultB as $keyB => $valB)
{
$address = $valB['address'];
$suburb = $valB['suburb'];
$cityId = $valB['city_id'];
$regionId = $valB['region_id'];
$countryId = $valB['country_id'];

// Get country data
if ($countryId !== NULL)
{
	$resultC = $db->select("select country_name from countries where country_id='$countryId'");

	foreach ($resultC as $keyC => $valC)
	{
		$countryName = $valC['country_name'];
	}
}

// Get region data
if ($regionId !== NULL)
{
	$resultC = $db->select("select region_name from regions where region_id='$regionId'");

	foreach ($resultC as $keyC => $valC)
	{
		$regionName = $valC['region_name'];
	}
}

// Get city data
if ($cityId !== NULL)
{
	$resultC = $db->select("select city_name from cities where city_id='$cityId'");

	foreach ($resultC as $keyC => $valC)
	{
		$cityName = $valC['city_name'];
		$cityName = htmlentities($cityName, ENT_NOQUOTES);
	}
}
} # foreach

 

This code is actually inside another loop so in effect 3 nested loops.  :-\

NB: $db->select() returns new assoc array from database.

 

Many thanks!

[jskywalker]

change the 'INNER JOIN' to a 'RIGHT JOIN'

 

see: http://dev.mysql.com/doc/refman/5.1/en/left-join-optimization.html

•If you use LEFT JOIN to find rows that do not exist in some table and you have the following test: col_name IS NULL in the WHERE part, where col_name is a column that is declared as NOT NULL, MySQL stops searching for more rows (for a particular key combination) after it has found one row that matches the LEFT JOIN condition.

The implementation of RIGHT JOIN is analogous to that of LEFT JOIN with the roles of the tables reversed

[Azsen]

Hmm tried that with this:

SELECT city_name, region_name, country_name
FROM locations
RIGHT JOIN countries ON locations.country_id = countries.country_id
RIGHT JOIN regions ON locations.region_id = regions.region_id
RIGHT JOIN cities ON locations.city_id = cities.city_id
WHERE locations.location_id = 87

 

But if I set either the city_id, region_id or country_id to NULL in the locations row it will return nothing in the query. But it should still return the name values where the id is set.

[jskywalker]

oeps, i did mean a 'LEFT JOIN'......

 

because you want all records from 'locations'

[Azsen]

Perfect! Saved me 60 lines of code just like that. 

 

Sometimes find it confusing when to use certain joins for what task so thanks very much!