Need help fixing the syntax for this LEFT OUTER JOIN

Question

I have a database structure like so:

SELECT * FROM Culture;
------------------------
Id  ShortName   FullName                Supported
22  en-US       English (United States) 1
23  fr-FR       French (France)         1
24  hi-IN       Hindi (India)           0

SELECT * FROM ResourceKey;
----------------------------
Id      Name
20572   HowAreYou
20571   Hello
20573   ThisKeyHasUSEnglishValueOnly

SELECT * FROM Strings;
-----------------------
Id      CultureId   ResourceKeyId           ResourceValue
41133   22          20571                   Hello
41134   22          20572                   How are you?
41135   23          20571                   Bonjour
41136   23          20572                   Comment allez-vous?
41137   22          20573                   This key has US English value only.    


SELECT * FROM Category;
------------------------
Id  Name
1   JavaScript


SELECT * FROM StringCategory;
------------------------------
Id  ResourceKeyId   CategoryId
1   20571           1
2   20572           1
3   20573           1

I want to display all resource key names and resource values, i.e. string values against each key, for, say, the French (France) culture, i.e. the culture with the ShortName fr-FR but even if a key does not have a value in the culture, it must display the key name but NULL for the value. Like so:

Name                            ResourceValue
-------------------------------------------------------
Hello                           Bonjour
HowAreYou                       Comment allez-vous?
ThisKeyHasUSEnglishValueOnly    NULL

It seems like a simple LEFT OUTER JOIN application to me, but my code isn't working. Could someone please help correct my code?

My query is:

SELECT ResourceKey.Name AS Name, ResourceValue
FROM
ResourceKey LEFT OUTER JOIN Strings
ON
Strings.ResourceKeyId = ResourceKey.Id
INNER JOIN StringCategory
ON
StringCategory.ResourceKeyId = Strings.ResourceKeyId
INNER JOIN Category
ON
StringCategory.CategoryId = Category.Id
LEFT OUTER JOIN Culture
ON
Strings.CultureId = Culture.Id AND Culture.Id = (SELECT Id FROM Culture WHERE ShortName = 'fr-FR')
AND
Category.Name = 'JavaScript';

Somehow, the last join in the above-query turns out to become an inner join, eliminating those rows where there is no value in the said culture.

Answer 1

LEFT JOINS can become inner joins when you specify criteria on the outer joined table . Try using a Left join to a subquery of culture containing your where clause instead of left joining to Culture.

LEFT JOIN (SELECT Id ,resourcevalue 
FROM Culture 
WHERE shortname = 'fr-FR') as CULTURE
ON 
Strings.CultureId = Culture.Id AND Culture.Id 

Full query

SELECT ResourceKey.Name AS Name, ResourceValue
FROM
ResourceKey LEFT OUTER JOIN Strings
ON
Strings.ResourceKeyId = ResourceKey.Id
INNER JOIN StringCategory
ON
StringCategory.ResourceKeyId = Strings.ResourceKeyId
INNER JOIN Category
ON
StringCategory.CategoryId = Category.Id
 LEFT JOIN (SELECT Id ,resourcevalue 
    FROM Culture 
    WHERE shortname = 'fr-FR') as CULTURE
    ON 
    Strings.CultureId = Culture.Id 
WHERE Category.Name = 'JavaScript';

Answer 2

SELECT  a.name, b.ResourceValue
FROM    ResourceKey a
        LEFT JOIN 
        (
            SELECT  b.ResourceKeyID, b.ResourceValue
            FROM    Strings b
                    INNER JOIN Culture c
                        ON b.CultureID = c.ID
            WHERE   c.shortname = 'fr-FR'
        ) b ON  a.ID = b.ResourceKeyId

UPDATED

USE SSTOBMAY;

SELECT  a.name, b.ResourceValue
FROM    ResourceKey a
        LEFT JOIN 
        (
            SELECT  b.ResourceKeyID, b.ResourceValue
            FROM    Strings b
                    INNER JOIN Culture c
                        ON b.CultureID = c.ID
            WHERE   c.shortname = 'fr-FR'
        ) b ON  a.ID = b.ResourceKeyId
        INNER JOIN
        StringCategory sc ON
        sc.ResourceKeyId = a.Id
        INNER JOIN Category c ON c.Id = sc.CategoryId
        WHERE c.Name = 'JavaScript';

• SQLFiddle Demo