CODEWITHSUNDEEP
mysql
Ivan Mihailov
Ivan Mihailov

Reputation: 21

MySQL receive only one record

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.

Upvotes: 1

Views: 68

Answers (5)

Ivan Mihailov
Ivan Mihailov

Reputation: 21

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.

Upvotes: 0

Matt Clark
Matt Clark

Reputation: 28609

Use the LIMIT argument in your query.

Upvotes: 0

Yogendra Singh
Yogendra Singh

Reputation: 34367

     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.

Upvotes: 0

Chris Trahey
Chris Trahey

Reputation: 18290

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:

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

Upvotes: 0

juergen d
juergen d

Reputation: 204766

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

Upvotes: 1

Related Questions