1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44
| SELECT
a.tao_id_taxon,
IF(EXISTS(SELECT 1 FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 33),
SELECT b.sta_nom_statut FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 33,
NULL) AS statut_uicn_fc,
IF(EXISTS(SELECT 1 FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 23),
SELECT b.sta_nom_statut FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 23,
NULL) AS statut_uicn_fr,
IF(EXISTS(SELECT 1 FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 31),
SELECT b.sta_nom_statut FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 31,
NULL) AS statut_uicn_monde,
IF(EXISTS(SELECT 1 FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 24),
SELECT b.sta_nom_statut FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 24,
NULL) AS statut_det_znieff,
IF(EXISTS(SELECT 1 FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 39),
SELECT b.sta_nom_statut FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 39,
NULL) AS statut_cat_patrimoniale,
IF(EXISTS(SELECT 1 FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 45),
SELECT b.sta_nom_statut FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 45,
NULL) AS statut_code_atlas,
IF(EXISTS(SELECT 1 FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 46),
SELECT b.sta_nom_statut FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 46,
NULL) AS statut_sp_invasive,
IF(EXISTS(SELECT 1 FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 47),
SELECT b.sta_nom_statut FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 47,
NULL) AS statut_sp_grand_territoire,
IF(EXISTS(SELECT 1 FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 48),
SELECT b.sta_nom_statut FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 48,
NULL) AS statut_orgfh,
IF(EXISTS(SELECT 1 FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 49),
SELECT b.sta_nom_statut FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 49,
NULL) AS statut_biologique,
IF(EXISTS(SELECT 1 FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 50),
SELECT b.sta_nom_statut FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 50,
NULL) AS statut_uicn_fr_hivernant,
IF(EXISTS(SELECT 1 FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 52),
SELECT b.sta_nom_statut FROM tr_statut b WHERE b.sta_id_statut = f.sta_id_statut AND b.tst_id_type_statut = 52,
NULL) AS statut_uicn_fr_passage
FROM t_taxon_observe a
INNER JOIN tj_taxon_a_statut f ON f.tao_id_taxon = a.tao_id_taxon
ORDER BY
tao_id_taxon |
Partager