id TINYINT(1) UNSIGNED NOT NULL,
uid INT(11) NOT NULL DEFAULT 0,
jobid INT(6) UNSIGNED NOT NULL DEFAULT 0,
- functionid SMALLINT(3) UNSIGNED NOT NULL DEFAULT 0,
sectorid TINYINT(2) UNSIGNED NOT NULL DEFAULT 0,
subsectorid TINYINT(3) UNSIGNED NOT NULL DEFAULT 0,
subsubsectorid SMALLINT(3) UNSIGNED NOT NULL DEFAULT 0,
SELECT entreprise, web
FROM entreprises;
-INSERT INTO profile_job (id, uid, jobid, description, email, pub, email_pub, functionid)
- SELECT e.entrid, e.uid, j.id, e.poste, e.email, e.pub, e.email_pub, e.fonction
- FROM entreprises AS e
- INNER JOIN profile_job_enum AS j ON (e.entreprise = j.name);
-
-DROP TABLE IF EXISTS profile_job_function_enum;
-
-CREATE TABLE IF NOT EXISTS profile_job_function_enum (
- id SMALLINT(3) UNSIGNED NOT NULL AUTO_INCREMENT,
- name VARCHAR(255) NOT NULL DEFAULT '',
- PRIMARY KEY (id),
- UNIQUE KEY (name)
-) CHARSET=utf8;
+INSERT INTO profile_job (id, uid, jobid, email, pub, email_pub, description)
+ SELECT e.entrid, e.uid, j.id, e.email, e.pub, e.email_pub,
+ CONCAT_WS(', ', IF(e.poste = '', NULL, e.poste), IF(e.fonction = 0, NULL, f.fonction_fr),
+ IF(e.ss_secteur IS NULL , IF(e.secteur IS NULL, NULL, s.label), ss.label))
+ FROM entreprises AS e
+ INNER JOIN profile_job_enum AS j ON (e.entreprise = j.name)
+ LEFT JOIN fonctions_def AS f ON (f.id = e.fonction)
+ LEFT JOIN emploi_ss_secteur AS ss ON (ss.id = e.ss_secteur)
+ LEFT JOIN emploi_secteur AS s ON (s.id = e.secteur);
-- vim:set syntax=mysql: