Frank Frank - 1 year ago 65
SQL Question

MySQL Conditional Where Clause

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'

I'm trying to figure out it even possible in MySQL to set up the query so that it would first look for: LANG.language = 'jp', and return the Japanese listing (as it does now), but if that comes up empty to then look for: LANG.language = 'en' and instead return that English listing if it exists.

So basically, return the Japanese listing if it exists, but if it doesn't then default to the English listing if that exists.

Answer Source

You could always do something like this:

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 en.

It's a big ugly, but it does succinctly capture the essence of what you're trying to do.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download