Thursday, March 8, 2012

Complicated conditional selection.

Befor question let me give you the tables, so simple:

tblProduct tblProductLang

ID ProductName Cost ID ProductName Lang
- -

1 Pencil 1 1 Bleistift gr-GR

2 Paper 1 2 Papier gr-GR

3 Button 2 3 Taste gr-GR

4 Mouse 15 4 Maus gr-GR

5 Monitor 45 4 Souris fr-FR

Ok. We have two table,
tblProduct: Keeps the product name and cost
tblProductLang: Keeps the product name in different language and short name of the language.

We are about the make a language file to our website. Default language is english. So no problem with the product name, user can easily understand. If yuser selects german , english names of the product will be nothing for him/her. We make a language table for products so when user changes language, he/she will get product name of the preffered language, if there is no equivalent name of that product, result will be default name. Let me give you a sample output:

If user wants gr-GR language , result will be: If user wants fr-FR language , result will be:

ID ProductName Cost ID ProductName Cost

- -

1 Bleistift 1 1 Pencil 1
2 Papier 1 2 Paper 1

3 Taste 2 3 Button 2

4 Maus 15 4 Souris 15

5 Monitor 45 5 Monitor 45


As you can see ID 1,2,3,4 came from tblProductLang as German, but ID 5 has no equivalent so it came from tblProduct as English.(Same thing for the french.) So question is how can i do that?

Here it is,

Code Snippet

Create Table #tblproduct(

[ID] int ,

[ProductName] Varchar(100) ,

[Cost] int

);

Insert Into #tblproductValues('1','Pencil','1');

Insert Into #tblproductValues('2','Paper','1');

Insert Into #tblproductValues('3','Button','2');

Insert Into #tblproductValues('4','Mouse','15');

Insert Into #tblproductValues('5','Monitor','45');

Create Table #tblproductlang (

[ID] int ,

[ProductName] Varchar(100) ,

[Lang] Varchar(100)

);

Insert Into #tblproductlang Values('1','Bleistift','gr-GR');

Insert Into #tblproductlang Values('2','Papier','gr-GR');

Insert Into #tblproductlang Values('3','Taste','gr-GR');

Insert Into #tblproductlang Values('4','Maus','gr-GR');

Insert Into #tblproductlang Values('4','Souris','fr-FR');

Code Snippet

Select P.ID, Isnull(L.ProductName, P.ProductName), P.Cost from #tblproduct P

left outer join #tblproductlang L

on P.[ID] = L.[ID] and [Lang] = 'fr-FR'

Select P.ID, Isnull(L.ProductName, P.ProductName), P.Cost from #tblproduct P

left outer join #tblproductlang L

on P.[ID] = L.[ID] and [Lang] = 'gr-GR'

|||YEEEES YEEEEEES. I love you man. I love you. I m crying right now. This is it. I ll never forget your name. I love youuuuu

No comments:

Post a Comment