MySQL receive only one record

Question

I am not sure that it is possible. My problem is to get only one row from database. Table described below:

CREATE TABLE IF NOT EXISTS `t_translate_content_pages` (
  `translate_content_page_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `content_page_id` int(10) unsigned NOT NULL,
  `language_code` varchar(3) NOT NULL,
  `content_page_title` varchar(255) NOT NULL,
  `content_page_text` text NOT NULL,
  PRIMARY KEY (`translate_content_page_id`),
  KEY `content_page_id` (`content_page_id`),
  KEY `language_id` (`language_code`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `t_translate_content_pages` 
(`translate_content_page_id`, `content_page_id`, `language_code`, `content_page_title`, `content_page_text`) VALUES
(3, 1, 'en', 'About', 'some text'),
(5, 1, 'ie', 'about', 'text');

I need to get only one row. If in database is record with language_code='ru' in result to have that record, if there is not to get record with language_code='en'. Will be very good to set this clause in WHERE clause of Sql query.

Regards to all.

score 1 · Answer 1 · answered Nov 12 '12 at 17:57

1

select * from t_translate_content_pages
where language_code in ('ru', 'en')
order by case when language_code = 'ru' then 1 else 2 end
limit 1

answered Nov 12 '12 at 17:57

juergen d

201,996
37
293
362

With just the one case, you can use IF: `ORDER BY IF(language_code = 'ru', 1, 2)` – Chris Trahey Nov 12 '12 at 18:05

Yogendra Singh · Answer 2 · 2012-11-12T18:06:18.437

0

     SELECT * from language_code 
     WHERE language_code = (
          SELECT DISTINCT language_code from t_translate_content_pages
          WHERE language_code in ('en', 'ru')
          ORDER BY language_code DESC
          LIMIT 1)
     LIMIT 1

The sub query gets the language code first and then get the matching records for it.

edited Nov 12 '12 at 18:06

answered Nov 12 '12 at 17:59

Yogendra Singh

33,927
6
63
73

score 0 · Answer 3 · answered Nov 12 '12 at 18:02

To get this, you need to sort based on the language preference (not the language itself). CASE is a great tool for this.

select * from t_translate_content_pages
ORDER BY CASE language_code
  WHEN 'ru' THEN 2
  WHEN 'en' THEN 1
  ELSE 0
END
DESC
LIMIT 1

Note that I intentionally did not limit the query to these languages, for two reasons:

I'm sure you know how to do that
It's worth considering this so that some language will be returned.

score 0 · Answer 4 · answered Nov 12 '12 at 18:12

0

Use the LIMIT argument in your query.

answered Nov 12 '12 at 18:12

Matt Clark

27,671
19
68
123

score 0 · Answer 5 · edited May 23 '17 at 10:24

I thonks I found much better solution. It is dscribet here: Best way to test if a row exists in a MySQL table

And SQL query look like this one

SELECT * 
FROM `t_translate_content_pages`
WHERE content_page_id = 1 AND language_code = IF(EXISTS(
  SELECT `translate_content_page_id` 
  FROM `t_translate_content_pages` 
  WHERE language_code = 'ru' AND `content_page_id` = `t_translate_content_pages`.`content_page_id`
), 'ru', 'en')

In this case I do not use LIMIT or ORDER clauses.

MySQL receive only one record

5 Answers5