| 21,12 → 21,12 |
|---|
| DROP PROCEDURE IF EXISTS majreferentiel; |
| |
| CREATE TEMPORARY TABLE IF NOT EXISTS T_bis ( INDEX(`nom`(30))) AS \ |
| SELECT "bdtfx" AS valid_ref, CONCAT(b.nom_sci, ' ', b.auteur) AS nom, b.num_nom, b.num_taxonomique, b.famille FROM `BASESOURCE`.`bdtfx_v1_01` b UNION ALL \ |
| SELECT "bdtxa" AS valid_ref, CONCAT(a.nom_sci, ' ', a.auteur) AS nom, a.num_nom, a.num_tax, a.famille FROM `BASESOURCE`.`bdtxa_v1_00` a; |
| SELECT "bdtfx" AS valid_ref, CONCAT(b.nom_sci, ' ', b.auteur) AS nom, b.num_nom, b.num_taxonomique, b.famille FROM `BASESOURCE`.`TABLEBDTFX` b UNION ALL \ |
| SELECT "bdtxa" AS valid_ref, CONCAT(a.nom_sci, ' ', a.auteur) AS nom, a.num_nom, a.num_tax, a.famille FROM `BASESOURCE`.`TABLEBDTXA` a; |
| |
| /* Donc nous JOINons: |
| -- INNER JOIN sur bdtfx: 69719 |
| SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, c.famille FROM `BASEEDIT`.`cel_obs` c INNER JOIN `BASESOURCE`.`bdtfx_v1_01` b ON (b.num_nom = c.nom_sel_nn) WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL; |
| SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, c.famille FROM `BASEEDIT`.`cel_obs` c INNER JOIN `BASESOURCE`.`TABLEBDTFX` b ON (b.num_nom = c.nom_sel_nn) WHERE nom_referentiel IS NULL AND nom_sel_nn IS NOT NULL; |
| -- INNER JOIN sur bdtxa: 79471 dont 9752 en dup (bdtxa + bdtfx), eg: 1005047 |
| SELECT id_observation, nom_referentiel, nom_sel, nom_sel_nn, nom_ret, nom_ret_nn, nt, c.famille FROM `BASEEDIT`.`cel_obs` c INNER JOIN T_bis b ON (b.num_nom = c.nom_sel_nn) WHERE nom_referentiel AND nom_sel_nn IS NOT NULL; |
| */ |