Help with simple SQL Query

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 follow开发者_StackOverflowing 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 ?

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.

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.

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