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?

This is identical to this question, requesting a different type of answer. There must be a way to combine these questions for future reference... — Dan J
– Dan J, Commented Sep 23, 2010 at 21:48

Daniel Vassallo · Accepted Answer · 2010-09-23 21:54:11Z

2

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         |
+------+--------+----------------+

edited Sep 23, 2010 at 21:54

answered Sep 23, 2010 at 21:45

Daniel Vassallo

346k72 gold badges513 silver badges447 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Help with simple SQL Query

Table A

Table B

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Table A

Table B

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related