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
