需要帮助修复此LEFT OUTER JOIN的语法

Water Cooler v2 发表于 Dev

水冷却器v2

我有这样的数据库结构：

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

我想显示所有资源键名称和资源值，即针对每个键的字符串值，例如，法国（法国）文化，即具有ShortName fr-FR的文化，即使键中没有值文化，它必须显示键名，但必须显示NULL。像这样：

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

对我来说，这似乎是一个简单的LEFT OUTER JOIN应用程序，但是我的代码无法正常工作。有人可以帮忙更正我的代码吗？

我的查询是：

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';

不知何故，上述查询中的最后一个联接变成了内部联接，从而消除了在所述区域性中没有值的那些行。

吴宇森

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

更新

使用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';