I'm try to output listings in a single query based on the specific language selected. But what I need to do is if the listing is not available for the selected language, then it would return the the English one if that exists.
So if English is 'en' and Japanese is 'jp', the following currently returns the Japanese listings:
FROM listings LS
LEFT JOIN listing_langs LANG ON LS.listing_id = LANG.listing_id
WHERE LANG.language = 'jp'
You could 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, explicitly check in the JOIN to see if there is no row for
jp and if not, then use
It's a big ugly, but it does succinctly capture the essence of what you're trying to do.