0

I have a table with products and another table with the names of these products (a product can have several names in different languages).

I currently use a query like this to list the names of all products:

SELECT name
FROM name_table
LEFT JOIN product_table ON name_table.product = product_table.product
ORDER BY name_table.product, language != 'en', language != '*'

This would yield a list of all names and the best available name (english if there is one, then an international name, if neither exists any name) would always appear first. I just skip over all entries after the first for each product - not elegant but it works fast enough.

The problem with this is that I cannot sort alphabetically.

However, when I use something like this:

SELECT
  product,
 (SELECT name
  FROM name_table
  WHERE name_table.product = product_table.product
  ORDER BY language != 'en'
  LIMIT 1) AS bestname
FROM product_table
ORDER BY bestname

then the query is extremely slow (at least with MySQL).

This query is fast but it seems to be undefined (therefore quasi-random) what name row is used:

SELECT name
FROM product_table
LEFT JOIN name_table ON name_table.product = product_table.product
ORDER BY name

If I only could tell MySQL what row to use for the join, then all would be fine.

Is there a way to define which row is used in a join if several matching rows exist?

Edit: Products can have names in any languages and not all products have an english name, sorry for the confusion.

Edit2: I have found an equivalent question: MySQL JOIN the most recent row only? There, the SQL-gurus also used sub-selects (my subselect-query is extremely slow so I want to avoid it), so maybe SQL cannot do what I want :-(

Community
  • 1
  • 1
Robby75
  • 3,285
  • 6
  • 33
  • 52

2 Answers2

1

Are you trying to get just the English names for the products? This your question suggests:

SELECT name
FROM name_table LEFT JOIN
     product_table ON name_table.product = product_table.product
where language = 'en'
ORDER BY name_table.product

Or, are you trying to get the foreign names, but order the products by their English name? If so, you need an additional join:

SELECT name
FROM name_table LEFT JOIN
     product_table 
     ON name_table.product = product_table.product and
        name_table.language <> 'en' left outer join
     product_table pten
     on name_table.product = product_table.product and
        name_table.language = 'en'
ORDER BY pten.name, name_table.product_id

This assumes that there is only one English name for a product. The second clause of the sort is for the case where there is no English name, to keep all the other names together.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • My impression is that you'd want to include the `language = 'en'` filter in the join condition of the first query also. – shawnt00 Jul 20 '12 at 18:18
  • Not all products have an english name, also there can be several english names for each product. (The real tables are a bit more complicated, there is also a priority-column, alias-flag, etc.) - Basically what I want to do is join not only a specific condition (LEFT JOIN ON bla = ble AND blo <> blu) but to use the best available that fits that condition. – Robby75 Jul 21 '12 at 06:33
0

The way you've defined your queries it doesn't appear you need to join to the product table at all.

If name_table has the product column, language column, AND the name you're trying to get, why is there a join to product_table? Please clarify, I think I might know what you need to do, but the question is worded in a confusing manner to me.

Aushin
  • 1,198
  • 1
  • 7
  • 12
  • Agreed. I think part of the confusion is whether all products have at least an English name. – shawnt00 Jul 20 '12 at 18:20
  • Sorry about that: Products can have no english name, one english name or several english names, it's completely arbitrary. Basically what I am trying to do is choose the best available name. – Robby75 Jul 21 '12 at 06:34