Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Location
    Inland Empire
    Posts
    18

    Unanswered: Conditional join

    I have a situation where I have a LanguageCulture table, containing iso codes for languages, and a table where each language-culture combination can connect to meta-data. Now, the situation exists where the culture value is non-existent, and the meta-data only has information for the language portion of the context, like so...

    LanguageCulture
    ------------------
    code lang culture
    ---- ----- -------
    en en NULL
    en-US en US
    en-GB en GB

    MetaData
    ------------------
    code data
    ---- ----
    en 'data 1'
    en-US 'data 2'


    Now, what I want to do is create a query with a join such that a query for code 'en' brings up the meta-data for 'en', a query for code 'en-US' brings up the meta-data for 'en-US', and a query for code 'en-GB' brings up the meta-data for 'en' (since there is no meta-data for the full culture, it goes to the meta-data for just the language portion of it).

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    SELECT lc.code
         , lc.lang
         , lc.culture
         , Coalesce(md1.data, md2.data) As [data]
    FROM   languageculture As [lc]
     LEFT
      JOIN metadata As [md1]
        ON lc.code = md1.code
     LEFT
      JOIN metadata As [md2]
        ON Left(lc.code, 2) = md2.code
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2003
    Location
    Inland Empire
    Posts
    18
    Perfect! I was looking to do this in the join, and couldn't figure it out. Thanks!

  4. #4
    Join Date
    Dec 2003
    Location
    Inland Empire
    Posts
    18
    I did make one small adjustment to keep from having to parse the string...

    SELECT lc.code
    , lc.lang
    , lc.culture
    , Coalesce(md1.data, md2.data) As [data]
    FROM languageculture As [lc]
    LEFT
    JOIN metadata As [md1]
    ON lc.code = md1.code
    LEFT
    JOIN metadata As [md2]
    ON lc.lang = md2.code

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •