Query to fall back to default if a WHERE value is null/blank

oops, sorry. posted wrong place

Thanks Guelphdad

OK, I’ll post the query here again. I was going to email it to a friend but I had two windows open and put it in the wrong one.

OK, so the last AND condition will contain a value.
the table may not contain a matching record. (might only have an english version, where the query wants French).
So I need to get the french version if it exists and if not, get the default (english) version. the distinguisher between one langauge and another is the language_abbr_this_file, value.


SELECT fd.file_name_other_language
      FROM file_sequencing AS fs
  INNER 
      JOIN file_data AS fd
        ON fd.file_id = fs.file_id								  
       and fs.live_from = (select max(b.live_from)
                                 from file_sequencing as b
                               where b.content_category = 'Pages'
                                  and b.live_from <= curdate()
                                  and fs.business_id = b.business_id
                                  and fs.file_name_in_home_language = b.file_name_in_home_language
                                 )
       and fs.sequence_number != '1000'
       and fs.content_category = 'Pages'
    where fs.business_id = ?
       and fd.language_abbr_this_file = ?
   
           order
               by fs.sequence_number

Blimey, I’m stupid.! yep it’s official.

If there is no file in a language, I don’t want the nav button to show at all. so the query I have is what I need.

bazz

If you have NULLs and not blanks/empty strings then you can use COALESCE between the two columns, the first non-null would be used.

In that case would you return the column instead of setting:

AND fd.language_abbr_this_file = 'ENG'

i’m not sure this helps without the table exactly but didn’t know if that was what you were overlooking.