Help with simple SQL Query

Question

Table A

Id    Name  
1     Apple
2     Mango
3     Banana

Table B

Id  Locale      Name_In_Lang
1   es-ES       Apple[Spanish]
1   it-IT       Apple[Italian]
2   it-IT       Mango[Italian]

Let us say if the user requested spanish versions then the query should return all the spanish [es-ES] verions from Table B for every record in Table A. If the spanish version is not available then simply return the corresponding record from Table A

The output should look something like -

Id  Locale                  Name
1   es-ES                   Apple[Spanish]      
2                           Mango
3                           Banana

Any suggestions on how to achieve it with and without using union?

Answer 1

It looks like you could use a LEFT JOIN as follows:

SELECT     ta.id, tb.locale, NVL(tb.name_in_lang, ta.name) name
FROM       tableA ta
LEFT JOIN  tableB tb ON (tb.id = ta.id AND tb.locale = ?)

The NVL() function lets you substitute a value when a null value is encountered.

The output for the es-ES locale would look like this:

+------+--------+----------------+
| id   | locale | name           |
+------+--------+----------------+
|    1 | es-ES  | Apple[Spanish] |
|    2 | NULL   | Mango          |
|    3 | NULL   | Banana         |
+------+--------+----------------+