all-habitats-search-listing

select *
from (
    select
        h.code_2000,
        TRIM(REPLACE(REPLACE(h.scientific_name, '[', ''), ']', '')) as scientific_name,
        h.habitat_category,
        CASE
            WHEN h.habitat_category = 'REDLIST' THEN REPLACE(h.habitat_unique_id, 'REDLIST_', '')
            WHEN h.habitat_category = 'ANNEX1' AND h.code_2000 != '' THEN h.code_2000
            ELSE h.eunis_habitat_code
        END AS eunis_habitat_code,
        h.habitat_type_tree,
        h.habitat_unique_id,
        CASE
            WHEN h.habitat_type_tree like '%RL - Marine habitats%' THEN 1
            ELSE 0
        END AS marine,
        REPLACE(h.habitat_unique_id, 'REDLIST', '') as redlist_code,
        CASE
            WHEN ls.id_dc = 2467 THEN CONCAT('/habitats_res4/', h.habitat_unique_id)
            WHEN h.habitat_category = 'ANNEX1' THEN CONCAT('/habitats/', h.habitat_unique_id)
            WHEN h.habitat_category = 'EUNIS2012' THEN CONCAT('/habitats_eunis2012/', h.habitat_unique_id)
            WHEN h.habitat_category = 'EUNISrev' THEN CONCAT('/habitats_eunis_revised/', h.habitat_unique_id)
            WHEN h.habitat_category = 'REDLIST' THEN CONCAT('/habitats_rl/', h.habitat_unique_id)
            ELSE '#'
        END AS habitat_link
    from EUNIS.latest.Habitat_Information h
    left join EUNIS.latest.Habitat_legal_status_mentioned ls
        on h.habitat_unique_id = ls.habitat_unique_id
       and ls.id_dc = 2467
) as a