Need help fixing the syntax for this LEFT OUTER JOIN

Water Cooler v2

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.

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

LEFT OUTER JOIN with subquery syntax

From Dev

LEFT OUTER JOIN with subquery syntax

From Dev

Syntax error on LEFT OUTER JOIN

From Dev

'WHERE' syntax equivalent of LEFT OUTER JOIN in PostgreSQL

From Dev

'WHERE' syntax equivalent of LEFT OUTER JOIN in PostgreSQL

From Dev

Need help writing a query (LEFT JOIN)

From Dev

Need help implementing a Full Outer Join in MS Access

From Dev

Need help in identifying and fixing SSLPeerUnverifiedException

From Dev

Oracle left-outer-join syntax shorthand notation (+) available in HQL?

From Dev

Oracle left-outer-join syntax shorthand notation (+) available in HQL?

From Dev

How to do a double left outer join in Linq query syntax(or fluent)

From Dev

Drools + Hibernate: Need to simulate Left Outer Join with OR condition in rule

From Dev

Need to understand specific LEFT OUTER JOIN behavior in SQL SELECT

From Dev

Old Style Oracle Outer Join Syntax - Why locate the (+) on the right side of the equals sign in a Left Outer join?

From Dev

LEFT OUTER JOIN with LIMIT

From Dev

Left Outer Join SOQL

From Dev

Outer apply and left join

From Dev

Django Left Outer Join

From Dev

LEFT OUTER JOIN problems

From Dev

Left Outer Join Issue

From Dev

LEFT (OUTER) JOIN

From Dev

Need help tuning query - left join using concatenated fields not efficient

From Dev

SQL JOIN and LEFT OUTER JOIN

From Dev

Combined Left Outer Join and Full Outer Join

From Dev

Right Outer Join to Left Outer join

From Dev

Need help fixing a game I made in Python

From Dev

I need help fixing this hangman function

From Dev

Need help fixing strange cin behavior

From Dev

Need Help Fixing A Bug In Snake Game

Related Related

HotTag

Archive