StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The problem is the Collation Sequence in use in your database. There is a huge amount of information about Collation Sequences out there depending on your RDBMS. You need to be very careful when changing the collation sequence as it can have unexpected consequences.</p> <p>The Collation Sequence is used to test the equality or inequality of columns. Depending on the Collation in use the following statements could all evaluate to either true or false:</p> <pre><code>'TexT' = 'text' 'TEXT' = 'text' 'áéíóú' = 'aeiou' </code></pre> <p>In your situation you would need to pick a Collation Sequence which would treat accented letters and regular letters as distinct values. Currently your database is determining that they are equal. Once you have that in place you can then develop your logic to select the appropriate value based on your prefered <code>culture</code></p> <p>Just to add it appears that your database is currently determining that <code>Paris = París</code>and I would suspect that there is no guarantee which distinct value will be selected, similar to the fact that there is no determined order of a results set unless an ORDER BY clause is used.</p> <p>I thought about this for a while. I think the best thing to do would be create a CultureRank </p> <pre><code>CREATE TABLE CultureRank ( Rank INTEGER, Culture VARCHAR(5) ); INSERT INTO CultureRank VALUES (1, 'es_ES'); INSERT INTO CultureRank VALUES (2, 'en_GB'); INSERT INTO CultureRank VALUES (3, 'pt_BR'); INSERT INTO CultureRank VALUES (4, 'pt_PT'); SELECT Name FROM City_i18n LEFT JOIN CultureRank ON City_i18n.Culture = CultureRank.Culture WHERE ID = 2745 AND City_i18n.Culture IN ('es_ES', 'pt_PT') -- If Required ORDER BY IF(ISNULL(CultureRank.Culture),1,0), CultureRank.Rank LIMIT 1; </code></pre> <p>The ORDER BY ISNULL is required if there will be cultures in city_i18n that are not in CultureRank. The IsNull function here will ensure that the cultures in the CultureRank table are given top priority, and then if none of those are associated with the city then the engine will select a random culture from the city_i18n table for that city. If you would prefer the cultures that are NOT in CultureRank to be the top priority then swap the 1 & 0 parameters in the ISNULL function.</p> <p>EDIT:</p> <pre><code>SELECT Name FROM City_i18n LEFT JOIN CultureRank ON City_i18n.Culture = CultureRank.Culture WHERE ID = 2745 AND City_i18n.Culture IN ('es_ES', 'pt_PT') -- If Required AND NOT EXISTS ( SELECT NULL FROM City_i18n Cities LEFT JOIN CultureRank CitiesRank ON Cities.Culture = CitiesRank.Culture WHERE City_i18n.Name = Cities.Name AND CultureRank.Rank < CitiesRank.Rank) </code></pre> <p>That should get you each Name from the City_i18n table for the given ID unless there is another record with the same name, based on your collation sequence, that has a higher rank in the CultureRank table. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload