MySQL Conditional Where clause

Question

I am trying to list in a single query based on the selected language. But I need to do, if the listing is not available for the selected language, then it will return English, if one exists.

So, if English is "en" and Japanese is "jp", the following Japanese lists are currently:

SELECT * 
FROM listings LS
LEFT JOIN listing_langs LANG ON LS.listing_id = LANG.listing_id
WHERE LANG.language = 'jp'

I am trying to figure out, even in MySQL, to configure a query so that it first looks for: LANG.language = 'jp' and returns the Japanese list (as it is now), but if that appears empty and then looks for: LANG.language = 'en' and instead returns this English list, if one exists.

So, in principle, return the Japanese list if it exists, but if it is not listed by default, if it exists.

Answer 1

You can always do something like this:

SELECT * 
FROM listings LS
LEFT JOIN listing_langs LANG ON (LS.listing_id = LANG.listing_id AND LANG.language = 'jp' OR 
    (NOT EXISTS (SELECT 1 FROM listing_langs WHERE listing_langs.listing_id = LS.listing_id AND listing_langs.language = 'jp') AND LANG.language = 'en')

That is, I explicitly check the JOIN to see if there is a line for jp, and if not, then use en.

It’s big ugly, but it eloquently captures the essence of what you are trying to do.

Answer 2

SELECT        * 
FROM          listings LS
  LEFT JOIN   listing_langs LANG
  ON          LS.listing_id = LANG.listing_id
WHERE         LANG.language = IFNULL(SELECT language FROM listing_langs WHERE language = 'jp','en')

Without testing (or testing capabilities), I think the IFNULLmysql command should be great for this.

Answer 3

Why don't you do

(SELECT *, 1 as grade  
FROM listings LS  
LEFT JOIN listing_langs LANG ON LS.listing_id = LANG.listing_id  
WHERE LANG.language = 'jp')   
UNION  
(SELECT *, 2 as grade   
FROM listings LS  
LEFT JOIN listing_langs LANG ON LS.listing_id = LANG.listing_id  
WHERE LANG.language = 'en')  
ORDER BY grade LIMIT 1

You will always have only one line if jpif exists, otherwise enif it does not exist.

Sorry for the previous error.