[SQL] SQL join with missing data

I have two tables, NAME and ADDRESS. For both tables, the primary key is ID and Language. I want to join these two tables on ID and Language, but sometimes there will be a case where the address table does not have a matching language for the ID, but there is a matching entry with a language set to English.

How can I get the join to return the matching language entry, or the English entry if there is no matching language?

I.e,

ID Language Name
A ENG Fred
A CYM Dref
B ENG Cane
B CYM Zark
ID Language Addr
A ENG Ad1
A CYM Ad2
B ENG Ad3

if I do select Name, Addr from NAME inner join ADDR on NAME.ID = ADDR.ID and NAME.Language=ADDR.Language, I get

Name Addr
Fred Ad1
Dref Ad2
Cane Ad3

if I do select Name, Addr from NAME left join ADDR on NAME.ID = ADDR.ID and NAME.Language=ADDR.Language

Name Addr
Fred Ad1
Dref Ad2
Came Ad3
Zark null

How do I get

Name Addr
Fred Ad1
Dref Ad2
Came Ad3
Zark Ad3

Continue reading...