Ich versteh den Sinn des Inner Join mit "languages l" nicht.
Such dir die l.lang_id raus wo der l.lang_code='en' ist und pack das direkt deine Joinbedingungen:
SQL-Code:
SELECT c.country_id AS countryId,
c.country_shortcode AS ISOShort,
c.country_longcode AS ISOLong,
25 AS LangId, --nur mal als Beispiel
cn.cn_name AS countryName,
rn.reg_name AS regionName,
sn.srn_name AS subregionName
FROM countries c
LEFT JOIN country_names cn ON cn.cn_cid = c.country_id AND cn.cn_lang = 25,
LEFT JOIN region_names rn ON rn.reg_id = c.country_region AND rn.reg_lang = 25,
LEFT JOIN subregion_names sn ON sn.sr_id = c.country_subregion AND sn.srn_lang = 25
ORDER BY c.country_id;
oder wenn du das wirklich so flexibler halten willst könntest du den INNER JOIN auch tatsächlich wie in der neuen Syntax üblich verwenden:
SQL-Code:
SELECT c.country_id AS countryId,
c.country_shortcode AS ISOShort,
c.country_longcode AS ISOLong,
l.lang_id AS LangId,
cn.cn_name AS countryName,
rn.reg_name AS regionName,
sn.srn_name AS subregionName
FROM countries c
INNER JOIN languages l on l.lang_code = "en"
LEFT JOIN country_names cn ON cn.cn_cid = c.country_id AND cn.cn_lang = l.lang_id,
LEFT JOIN region_names rn ON rn.reg_id = c.country_region AND rn.reg_lang = l.lang_id,
LEFT JOIN subregion_names sn ON sn.sr_id = c.country_subregion AND sn.srn_lang = l.lang_id
ORDER BY c.country_id;