mysql join 条件,MySQL-使用条件执行JOIN

I have 2. tables: videos and videos_lang. I join these tables with LEFT JOIN when I want to get information about a video in selected language. And here is the problem: if the translation in selected language doesn't exist, I want to join translation in default language (which always exists). How can I do that?

I use that sql query:

SELECT vl.\*, v.*

FROM videos v

LEFT JOIN videos_lang vl

ON vl.sig = 'de' AND v.idVideo = vl.idVideo

How to get translation where vl.sig = 'en' (en is the defualt language)?

解决方案

Try this query -

SELECT

IFNULL(vl.idVideo, vl_en.idVideo),

v.*

FROM videos v

LEFT JOIN (SELECT * FROM videos_lang WHERE sig = 'de') vl

ON v.idVideo = vl.idVideo

LEFT JOIN (SELECT * FROM videos_lang WHERE sig = 'en') vl_en

ON v.idVideo = vl_en.idVideo