all-sites-national-search-listing

SELECT 
    si.code_site AS site_code,
    si.site_name,
    si.country_name,
    si.site_type,
    dt.designation_type_name_english AS designation,
    CONCAT('/sites/national/', si.code_site) AS site_link
FROM EUNIS.latest.Site_Information si
LEFT JOIN EUNIS.latest.Natda_designated_site nds
    ON CAST(nds.natda_id AS VARCHAR) = si.code_site
LEFT JOIN EUNIS.latest.Natda_designation_type dt
    ON dt.designation_type_code = nds.designation_type_code
WHERE si.site_type = 'Nationally designated area'
ORDER BY country_name