0

I have a table with products with names in different languages:

product-id | lang-no | name

Now I want to generate product lists with names in each language. Unfortunately I don't have all names in all languages and must fall back to other languages if the right one isn't availabe.

Getting a list with a name in any language is easy:

SELECT DISTINCT product-id, name FROM mytable;

Is it defined which row gets picked by the database for the distinct product-id ? (MySQL in my case.)

How can I generate a list for language 1 and one for language 2 ?

Bonus: Can I define which language to fall back on ?

Update:

So my initial query is wrong. (Thanks!)

My list needs to include all products.

SELECT DISTINCT product-id FROM mytable

Now I need to add the names, preferrablx in one language, but no one language covers all products.

  • 3
    This query selects distinct "product-id, name" combinations, not distinct product_id values. – ypercubeᵀᴹ Oct 23 '15 at 17:58
4

Your query will give all unique combinations of product-id and name in the table, so if for example product id 123 has name "Pencil" in English and "Crayon" in French, you will get:

123 | Pencil
123 | Crayon

The results are deterministic in that all unique combinations that exist in your table, will be returned in that query. There isn't an option of which row will be picked, as the list of columns in the query determines what you want the results to be "unique" by. For example if you have product ID 123 with "Pencil" in both British English and American English, the query will return just the single one 123 - Pencil.

To get a list for language 1 (let's say I want it in English and that is language number 567):

select distinct product-id, name from mytable where lang-no = 567

Please clarify how the "fallback" language should work - do you have a specific language in which you know you will always have ALL products? Once you clarify that we can edit the answer accordingly.

  • Unfortunately there is no one language that covers all products. If possible I want to list all products say in Italian and iif that language isn't avilable use English or any other language. – Gene Vincent Oct 23 '15 at 20:26
  • You need a way to "prioritise" the languages -- for example if I want a pencil in Italian but the database only has the English and French (as in the example I gave) - which language (English or French) should be picked in that case and why? – seventyeightist Oct 25 '15 at 13:39
  • I could come up with a long cascade of IF THEN ELSE to pick a language, but for simlicity of the example, maybe you can explain how to pick the names using MIN(lang-no) as ranking ? Thanks! – Gene Vincent Oct 25 '15 at 16:04
1

From Bill Karwin's answer of Aug 30 '11 in this question/answer: https://stackoverflow.com/questions/7250566/mysql-select-distinct

DISTINCT is not a function that applies only to some columns. It's a query modifier that applies to all columns in the select-list. That is, DISTINCT reduces rows only if all columns are identical to the columns of another row.

So it is a pretty distinct function to use.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.