Help with simple SQL Query

Question

Table A

Id    Name  
1     Apple
2     Mango

Table B

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

Join the table and get the following output:

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

I have the following query...

Select a.id, b.locale, b.name_in_lang, a.name 
from TableA a
Left Outer Join TableB b on ( a.id = b.id)

... and I only get:

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

Any suggestions ?

Answer 1

You can union your actual results with the extra rows desired like so:

SELECT a.id, b.locale, b.name_in_lang, a.name
FROM TableA a
LEFT OUTER JOIN TableB b ON a.id = b.id
UNION
SELECT a.id, '' AS locale, '' AS name_in_lang, a.name
FROM Table a

The second query in the UNION will supply one row per record in table A, with an empty locale and translated name.

Answer 2

If you want to add one row for each entry in TableA, you could just use a UNION clause:

SELECT 
    a.id, b.locale, b.name_in_lang, a.name 
FROM 
    TableA a
LEFT OUTER JOIN
    TableB b ON a.id = b.id

UNION

SELECT 
    id, '', '', name 
FROM 
    TableA 

That would add one entry without any locale information to your result set.

Answer 3

SELECT a.Id, NULL AS Locale, NULL AS Name_IN_Lang, a.Name
    FROM TableA a
UNION ALL
SELECT a.Id, b.Locale, b.Name_In_Lang, a.Name
    FROM TableA a
        INNER JOIN TableB b
            ON a.Id = b.Id
ORDER BY Id, Name, Locale