All we need is an easy explanation of the problem, so here it is.
There are two tables, namely
country without any matching columns. I need to retrieve a unique field
printable_name from country table based on the value of
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:
part of sku table is:
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.
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;
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
sku and will quite literally join each record with everything, resulting in a lot of "bad" data.
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 🙂