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 |
You need to sign in to view this answers
Leave feedback about this