I'm new to MySQL, so forgive me if the question is stupid. I've got 2 (in fact 3) tables, loaded with datas from csv files, like this :

-- Création de la base
CREATE DATABASE PaysVillesLangues CHARACTER SET 'utf8';

USE PaysVillesLangues;

-- Création table pays
CREATE TABLE pays (
  code CHAR(3) NOT NULL,
  nom_pays VARCHAR(255) NOT NULL,
  continent ENUM('Asia','Europe','North America','Africa','Oceania',
                 'Antartica','South America')
           NOT NULL,
  region VARCHAR(255) NULL,
  aire DECIMAL(9,2) NULL,
  independance INT NULL,
  population INT NULL,
  esperance_vie DECIMAL(2,1) NULL,
  gnp DECIMAL(12,2) NULL,
  gnp_ancien DECIMAL(12,2) NULL,
  nom_local VARCHAR(255) NULL,
  gouvernement VARCHAR(255) NULL,
  chef_etat VARCHAR(255) NULL,
  capitale INT NOT NULL,
  code2 VARCHAR(2) NULL,
  PRIMARY KEY (code)
  );

-- Chargement des données depuis le fichier pays.csv
LOAD DATA LOCAL 
  INFILE '/mnt/travail/Lycee/2019/Formation NSI/Projet BDR/MySQL/pays.csv' 
  INTO TABLE pays 
  FIELDS TERMINATED BY ';' 
  LINES TERMINATED BY '\n';

-- Création de la table langues
CREATE TABLE langues (
  code_pays CHAR(3) NOT NULL,
  langue VARCHAR(100) NOT NULL,
  officiel ENUM('T','F') NULL,
  pourcentage DECIMAL(3,1),
  PRIMARY KEY (langue)
  );

-- Chargement des données depuis le fichier langues.csv
LOAD DATA LOCAL 
  INFILE '/mnt/travail/Lycee/2019/Formation NSI/Projet BDR/MySQL/langues.csv' 
  INTO TABLE langues 
  FIELDS TERMINATED BY ';' 
  LINES TERMINATED BY '\n';

So I have those two tables, containing informations about countries and languages. I want to find all countries where French is spoken. So I type this request :

SELECT nom_pays
  FROM pays INNER JOIN langues ON pays.code = langues.code_pays
  WHERE langue = 'French';

But I don't get any informations (I should get 24 rows).

What did I do wrong ?

If you can help, I would be eternally grateful :-)

-- Nicolas FRANÇOIS

3 Answers

0
Rahul Singh On

This happening because of load data infile. As you load value into your table it adds the space after the values.

So try to update using trim after load data infile.

UPDATE langues SET code_pays=TRIM(code_pays),langue=TRIM(langue),officiel=TRIM(officiel), pourcentage=TRIM(pourcentage);

Do the same thing to your pays table

Thank You.

-1
symcbean On

If there was something intrinsically wrong with your SQL you would likely have got an ugly error message - and looking at what you've presented here, I see nothng wrong with it, so if your query is not giving the results you expect, then its probably an issue with the data.

Try unrolling the join to find the problem:

SELECT pays_code, langue
FROM langues
WHERE langue = 'French';

Take a note of the codes returned (if any) then use a couple of them in place of "'a','b'" in....

SELECT code, nom_pays
FROM pays
WHERE code IN ('a', 'b')
WHERE code IN (1,2,3);

From memory, LOAD DATA INFILE is rather temperamental ad does not report all failures as errors - you need to poll the warnings.

1
Nicolas FRANCOIS On

Update : I solved my problem with your help, thanks.

There was no particular problems with unwanted spaces in the csv files, but there were several inconsistencies in the files (misplaced fields...).

Thanks for your help in debugging my case.

\bye

--

Nicolas FRANÇOIS