Mysql – Which row is selected by DISTINCT

distinctMySQLselect

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.

Best Answer

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.

Related Solutions

Mysql – Avoiding Multiple Queries when Searching for Records Associated with a Set of Records

If I understand you correctly, you want to display the products regardless if they are associated with a price rule, right ? In my opinion, you'd best achieve this with an OUTER JOIN, which will give you all products and simply return an empty PriceRuleId column for the products without price rules.

The following query will give you a result Matrix with all products and their price rules, if they have them.

select 
     p.id as ProductId
    ,pr.id as PriceRuleId
    ,pr.apply_to as PriceRuleApplyTo
    ,pr.start_date as PriceRuleStartDate
    ,pr.end_date as PriceRuleEndDate
    ,pr.promo_code as PriceRulePromoCode
from
    products p
    LEFT OUTER JOIN price_rule_product prp ON prp.product_id = p.id
    LEFT OUTER JOIN
        ( SELECT id, apply_to, start_date, end_date, promo_code
            FROM  price_rule
            WHERE 
                apply_to = 'line_items'
                AND ( start_date IS NULL OR start_date = 0 OR start_date <= 1366063902 )
                AND ( end_date IS NULL OR end_date = 0 OR end_date >= 1366063902 )
                AND ( promo_code IS NULL OR promo_code = '' )
            ) pr ON prp.price_rule_id = pr.id

I hope I understood the problem correctly.

MySQL Grouping Order

You can use ORDER BY inside GROUP_CONCAT() function. You can also change the separator, if you don't want to use comma. Further details in MySQL documentation: GROUP_CONCAT()

Example:

SELECT 
    product_id,
    GROUP_CONCAT(name 
                 ORDER BY language_id
                 SEPARATOR ','
                ) AS product_names
FROM
    products_languages
GROUP BY
    product_id ;

Best Answer

Related Solutions

Mysql – Avoiding Multiple Queries when Searching for Records Associated with a Set of Records

MySQL Grouping Order

Related Question