Please help with a query


  • Please help create a query from the following approx. tables/datasets:

    Table 1:

    ProductID CategoryID   CategName  SubCategoryID  SubCategoryName    ....

    ID1          CatID1          CatName1     SubCatID1       SubCatName1          ...

    ID2          CatID2           CatName2    SubCatID2       SubCatName2          ...

    ID3          CatID3           CatName3    SubCatID3       SubCatName3          ...

    ....            ......                 ......           ......                 ......

    Translation Table:

    CategoryID       CategoryName    SubCategoryID    SubCategoryName   .... Translation   LanguageName LangID

    CatID1                CatName1               NULL     NULL        .... TranslName1          Spanish  11                                     

    NULL                   NULL           SubCatID1 SubCatName1  .... TranslName2          Spanish  11  

    .... ..... .....

    Language Table:

    LangID LanguageCode

    11         3082

    I'd need to get the Table 1 translated to show all data from there but showing e.g. CategoryName and SubCategoryname in Spanish.

    Please don't suggest to change the tables/datasets structure as we don't have an opportunity to do it.




Все ответы

  • Hi,

    The requirement is not clear to me. can you post some sample data and the a query you have? We can help you fix the query


  • If you a want a word-by-word translation from English to Spanish there is no native way to do this in TSQL. What you can do is to develop a CLR function and call the function in TSQL and do the translation. Here is a link that may be helpful:

    A CLR Trnslating function based on Google translator

    Krishnakumar S

    • Предложено в качестве ответа Naomi NModerator 17 мая 2012 г. 16:56
  • Thanks for your responses. I just need a query. I'm trying two options as below - the first one is to get it as a nested query that is simple but probably not efficient as it can slow the query; the second one to use several left outer joins to Translation table with where ="Spanish" not even bringing the third Language table where it's 3082 (as the third table can slow the query a bit as wel but probably would be a proper way) .

    Please, advise what would be a "good" query for this.


    category AS categoryid



                when (select count(translation) from Translations t

                join Translationlanguage tl on t.LanguageId=tl.translationlanguageId

                where tl.LanguageCode=3082 AND category=t.CategoryId) >0 then

                (select translation from Translations t

                join Translationlanguage tl on t.LanguageId=TranslationlanguageId

                where tl.LanguageCode=3082 AND Category=t.CoiCategoryId     )

                else categoryname


                AS categoryname





    Or I’m trying the query below:


    ) C

    LEFT OUTER JOIN Translations TF ON C.CategoryID=TF.categoryid

    LEFT OUTER JOIN Translations TT ON C.SubcategoryID=TT.subcategoryid

    WHERE  TF. languageidname = 'Spanish' OR TT.languageidname = 'Spanish'

  • I resolved it using the following tip about joins with parentheses:

    • Помечено в качестве ответа al-dol 9 июня 2012 г. 18:52
    9 июня 2012 г. 18:52
  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    Because you are rude and lazy, we have to do your work for you. Your design is a total mess! There is no such thing as a category_id, etc. What kind of category system do you have? An atteibute can be a <something>_category> or a <something>_id” but not that vague mess you have. Since you also did bother with sample data my guess as to what they are like is the Dewy Decimal Classification. Are you are using UPC or other industry standstill for the product ids?  Can a product in more than one category? Etc. 

    CREATE TABLE Product_Categories
     product_category CHAR(7) NOT NULL
       CHECK (product_category LIKE '[0-9][0-9][0-9].[0-9][0-9][0-9]'),
     eng_product_category_name VARCHAR(20) NOT NULL,
     esp_product_category_name VARCHAR(20) NOT NULL);

    Talk tsomeone from Canada; you keep multiple translations with the entity to which they apply. Why did you not use the ISO Standard language codes? 

    >> Please don't suggest to change the tables/datasets structure as we don't have an opportunity to do it. << 

    Then you are dead. Your design failures will accumulate until your app falls apart.  Remember what I do for a living for past few decades and think about it. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    9 июня 2012 г. 23:27
  • Thank you so much CELKO for your post. As I mentioned above it's already solved. I posted a link also that actually helped me to resolve it. There is a piece of code below I did apply that works fine for me:


    FROM      Topiccategory THC
                          LEFT OUTER JOIN (Translations T
                          INNER JOIN Translationlanguage tl on t.LanguageId=tl.translationlanguageid AND tl.LanguageCode=3082
           ON THC.topiccategoryid=T.topiccategoryid 

    10 июня 2012 г. 0:07