Please help with a query
-
17 мая 2012 г. 6:34
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.
Thanks
Все ответы
-
17 мая 2012 г. 7:05
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
Regards
Satheesh -
17 мая 2012 г. 7:11
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 NMicrosoft Community Contributor, Moderator 17 мая 2012 г. 16:56
-
17 мая 2012 г. 17:07
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.
SELECT DISTINCT
category AS categoryid
,
case
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
end
AS categoryname
FROM
(
SELECT
....
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'
-
9 июня 2012 г. 18:52
I resolved it using the following tip about joins with parentheses:
http://www.bennadel.com/blog/1059-Grouping-JOIN-Clauses-In-SQL.htm
- Помечено в качестве ответа al-dol 9 июня 2012 г. 18:52
-
9 июня 2012 г. 23:27Please 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
(upc CHAR(13) NOT NULL PRIMARY KEY,
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
-
10 июня 2012 г. 0:07
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
.png)
