How to efficiently SELECT a column's field based on another row with the same `id`?

Question

Here is my table:

 id | title    | lang
----+----------+------
 1  | Moscow   | en
 1  | Москва   | ru
 2  | Helsinki | en 
 2  | Хельсинки| ru

I would like to efficiently get the ru title by en title.

At the moment, I get the id of the entry first and then make another query by hand.

Any other, more elegant, solutions?

Does not clear your question actually what you want, `english title?` — Sadikhasan, Jul 27 '15 at 08:42
Your question is unclear. Could you please show us what you have tried thus far? — PKirby, Jul 27 '15 at 08:44

score 4 · Accepted Answer · edited May 23 '17 at 12:30

4

A SELF JOIN might be of help and is usually a preferable solution to lots of nested queries, if not for performance reasons (see: Join vs. sub-query, Rewriting Subqueries as Joins) certainly for readability.

In your case try:

SELECT movies_ru.title 
 FROM movies AS movies_ru 
 JOIN movies AS movies_en 
 ON movies_ru.id = movies_en.id
 WHERE movies_ru.lang = "ru"
 AND movies_en.lang = "en"
 AND movies_en.title = "The English Title"

edited May 23 '17 at 12:30

Community

1
1

answered Jul 27 '15 at 08:46

Tobia Tesan

1,938
17
29

2

After reading "Join vs sub-query" I have to agree... @DainiusVaičiulis , are you hearing us? – Siguza Jul 27 '15 at 11:03
@Siguza *nota bene*: I seem to understand that this is not an universal rule; according to benchmarks found on some Internet outlets, under Oracle, for example, there *seems* to be little or no penalty for nested `SELECT`s over self `JOIN`s. However, I think it's safe to take the MySQL reference manual at face value when it says *"A LEFT [OUTER] JOIN can be faster than an equivalent subquery because the server might be able to optimize it better"* – Tobia Tesan Jul 27 '15 at 11:22

Siguza · Answer 2 · 2015-07-27T11:05:20.610

1

Edit: It turns out Tobia Tesan's answer is usually better practice.

Use a subselect:

SELECT `title` FROM `table` WHERE `lang` = 'ru' AND `id` = (SELECT `id` FROM `table` WHERE `lang` = 'en' AND `title` = 'Moscow')

edited Jul 27 '15 at 11:05

answered Jul 27 '15 at 08:45

Siguza

21,155
6
52
89

score -1 · Answer 3 · edited Jul 27 '15 at 09:07

-1

you can do this whit this simple query:-

SELECT title FROM table 
WHERE lang = 'ru'
 AND
 id = (SELECT id FROM table WHERE lang = 'en' AND title = 'Moscow')

edited Jul 27 '15 at 09:07

Sergio Tulentsev

226,338
43
373
367

answered Jul 27 '15 at 09:02

david sam

521
1
8
25

Except `table` is a keyword, and needs to be escaped, or you'll hit a syntax error. – Siguza Jul 27 '15 at 10:26

How to efficiently SELECT a column's field based on another row with the same `id`?

3 Answers3

Linked