Return a default row if nothing is found

Question

I have a query that will return data assuming members.language_id is greater than zero and a supported language. If a member does not have a language selected members.language_id defaults to zero, which isn't helpful when joining on another table (keep in mind I didn't design this table structure and I can't change it).

So, what I would like to do is when no result comes back default to a result where:
t.code = 'Kount_Declined' and t.language_id = 1

select m.email, t.subject, t.message from orders o
join members m using(member_id)
join languages l using(language_id)
join emailtempl t on t.language_id = GREATEST(l.language_id, 1)
where o.transaction_id = '3TK904KVMM8X'
and t.code = 'Kount_Declined'

Is there any way that I can do this? I understand that I could do this with 2 queries, but is there a way to do it without the extra query?

Answer 1

Thanks to @RyanVincent for suggesting that I use a union with a limit 1, I was then able to come up with this, and it has passed all the tests that I was looking for to accomplish this feat.

(
    select t.language_id, m.email, t.subject, t.message from orders o
    join members m using(member_id)
    join languages l using(language_id)
    join emailtempl t using(language_id)
    where o.transaction_id = '3TK904KVMM8X'
    and t.code = 'Kount_Declined' limit 1
)
union
(
    select t.language_id, m.email, t.subject, t.message from orders o
    join members m using(member_id)
    join emailtempl t on t.language_id = 1
    where o.transaction_id = '3TK904KVMM8X'
    and t.code = 'Kount_Declined' limit 1
)
limit 1

Answer 2

You could use an IF

select m.email, t.subject, t.message
from orders o
join members m using(member_id)
join emailtempl t on o.language_id = IF(t.language_id = 0, 1, t.language_id)
where o.transaction_id = '3TK904KVMM8X'
and t.code = 'Kount_Declined';

Answer 3

Try a modified join condition

    select m.email, t.subject, t.message
      from orders o
      join members m      using(member_id)
      join emailtempl t   on t.language_id = GREATEST(o.language_id, 1)
     where o.transaction_id = '3TK904KVMM8X'
       and t.code = 'Kount_Declined'
         ;