Left Join Statement returns multiple rows instead of one

All we need is an easy explanation of the problem, so here it is.

There are two tables, namely sku and country without any matching columns. I need to retrieve a unique field printable_name from country table based on the value of c_code in sku table. The result from my SQL statement below gives multiple rows instead of just one. I just want the record only from country table.

I am trying to combine the following SQLs into one with the JOIN statement so that I get country name with a single SQL statement.

$vendor_sku = $my_line_item['sku'];
                    
                    // Build SQL to retrieve country name.
                    $sql = "SELECT c_code FROM sku WHERE item_sku = '" . $vendor_sku . "'";
                    $sql = "SELECT printable_name FROM country WHERE numcode = '" . $c_code . "'";

SELECT country.printable_name
  FROM country INNER JOIN sku ON country.numcode = sku.c_code
 WHERE country.numcode = "124"

part of country table is:

Left Join Statement returns multiple rows instead of one

part of sku table is:

Left Join Statement returns multiple rows instead of one

Left Join Statement returns multiple rows instead of one

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

As you say there are no matching rows between the two tables, you cannot retrive a unique value from country table based on sku.numcode. You need to add a foreign key on sku table which matches the country table, using some unique value ( perhaps iso or numcode, better if it’s the int field numcode, but this needs to be unique in this table ).

If there’s an error in your explanation, and sku.c_code matches country.numcode, you can do:

select c.printable_name from country c inner join sku s on c.numcode = s.c_code where s.c_code = ‘124’ limit 1;

Method 2

Your SQL query is incomplete. Try this:

SELECT country.printable_name
  FROM country INNER JOIN sku ON country.numcode = sku.c_code
 WHERE country.numcode = "124"

Without this, the database engine doesn’t know how to join country with sku and will quite literally join each record with everything, resulting in a lot of "bad" data.

Method 3

As ypercube mentioned in the comments, if you’re only looking for the printable_name from the country table for a specific numcode, then you don’t even need to join to the sku table, rather you can just filter the country table down with a WHERE clause like so:

SELECT printable_name 
FROM country 
WHERE numcode = '124';

Otherwise, if you’re trying to get the printable_name of each distinct row in country where the numcode exists in the c_code column of the sku table, then you can use a CTE to get the distinct list of c_code first, then join to it like so:

WITH DistinctCCodes AS
(
    SELECT DISTINCT c_code
    FROM sku
)

SELECT C.printable_name 
FROM country C
INNER JOIN DistinctCCodes D
    ON C.numcode = D.c_code;

Another way to accomplish the same outcome as the above without a CTE, is to just use an inline subquery in the WHERE clause (though I find this can be sub-performant sometimes):

SELECT printable_name 
FROM country
WHERE numcode IN
(
    SELECT DISTINCT c_code
    FROM sku
);

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply