Salary Market

use salarymarket;

CREATE TABLE solution_setting_country (

id INT AUTO_INCREMENT,

code TEXT NOT NULL PRIMARY KEY,

active boolean,

);

CREATE TABLE solution_setting_country_translate (

id  TEXT NOT NULL PRIMARY KEY,

code  TEXT, 

Libele TEXT,

languageCode INT NOT NULL,

FOREIGN KEY (code) REFERENCES solution_setting_country(code) ON DELETE CASCADE

);

--solution_setting_country 

(1, 'TN', NULL, 1),

(1, 'FR', NULL, 1),

--solution_setting_country_translate

('TN1' ,'TN', 'TUNISIE', 1),

('TN2' ,'TN', 'TUNISIA', 2),

('FR1' ,'FR', 'FRANCE', 1),

('FR2' ,'FR', 'FRANCE', 2),

SELECT p.code, t.Libele

FROM solution_setting_country p

JOIN solution_setting_country_translate t ON p.code = t.code

WHERE t.LanguageCode = 'fr';

CREATE TABLE solution_setting_town(

id INT AUTO_INCREMENT,

code TEXT NOT NULL PRIMARY KEY,

countryCode TEXT NOT NULL,

FOREIGN KEY (code) REFERENCES solution_setting_country(countryCode) ON DELETE CASCADE

);

CREATE TABLE solution_setting_town_translate (

id  TEXT NOT NULL PRIMARY KEY,

code  TEXT, 

Libele TEXT,

languageCode INT NOT NULL,

FOREIGN KEY (code) REFERENCES solution_setting_country(code) ON DELETE CASCADE

);

--solution_setting_country_town

(2, 'TUNIS', 'TN', 1),

(2, 'PARIS', 'FR', 1),

--solution_setting_country_town_translate

('TUNIS1' ,'TUNIS', 'TUNIS', 1),

('TUNIS2' ,'TUNIS', 'TUNIS', 2),

('PARIS1' ,'PARIS', 'PARIS', 1),

('PARIS2' ,'PARIS', 'PARIS', 2),

SELECT p.code, t.Libele

FROM solution_setting_town p

JOIN solution_setting_town_translate t ON p.code = t.code

WHERE t.LanguageCode = 'fr'

AND p.countrycode = 'TN';