MySQL query to fetch data by lang_id (if not fetch by default lang_id = 0)

MySQL

I have a following table

CREATE TABLE `lang_pages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lang_id` int(11) NOT NULL,
  `page_id` int(11) NOT NULL,
  `page_title` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

insert into lang_pages set lang_id = 0, page_id = 1, page_title = 'About Us (English)';
insert into lang_pages set lang_id = 1, page_id = 1, page_title = 'About Us (German)';
insert into lang_pages set lang_id = 2, page_id = 1, page_title = 'About Us (French)';
insert into lang_pages set lang_id = 0, page_id = 2, page_title = 'Contact Us (English)';

I need a MySQL query which fetches the page_title by page_id & lang_id, if not present it should return the one with lang_id = 0 using single query.

I have tried it as below which works fine

select * from lang_pages where page_id = <page_id> and lang_id in (0, <lang_id>) order by lang_id desc limit 1;

For example:

select * from lang_pages where page_id = 1 and lang_id in (0, 1) order by lang_id desc limit 1;

This works just fine in case you are adding page_id in the condition.
(You are welcome if you have another approach for this)

PROBLEM
How would you find all the pages with certain lang_id, say 1? The result will be like If row exists get page from that lang_id else from lang_id = 0

Best Answer

What you have is fine.

Another way to write this (assuming that for every page_id, there is always a row with the default lang_id = 0), is to use a LEFT JOIN. There should be minimal if any difference in performance but this is easily extendable for multiple requests:

select d.page_id,
       coalesce(p.lang_id, d.lang_id)       as lang_id,     -- ours or the default
       coalesce(p.page_title, d.page_title) as page_title   -- ours or the default
from lang_pages as d               -- the default
  left join lang_pages as p        -- our language
    on  p.lang_id = <lang_id> 
    and p.page_id = d.page_id
where d.lang_id = 0 
  and d.page_id = <page_id>             -- one page or
--  and d.page_id in (1, 3, 7, ...)     -- possible other criteria
 ;
Related Question