mysql - SQL join table to self to find difference -

consider below table

create table `temp` (   `id` int(11) not null,   `lang` char(2) collate utf8_unicode_ci not null,   `channel` char(2) collate utf8_unicode_ci not null,   `name` varchar(20) collate utf8_unicode_ci default null,   primary key (`id`,`lang`,`channel`) )  insert `temp` (`id`, `lang`, `channel`, `name`) values('1','fr','ds','jacket'); insert `temp` (`id`, `lang`, `channel`, `name`) values('1','en','ds','jacket'); insert `temp` (`id`, `lang`, `channel`, `name`) values('2','en','ds','jeans'); insert `temp` (`id`, `lang`, `channel`, `name`) values('3','en','ds','sweater'); insert `temp` (`id`, `lang`, `channel`, `name`) values('1','de','ds','jacket'); 

the question how can find entries lang en not exist fr? head stuck , believe trivial query having 1 of these days.

there several ways of achieving this. 1 way use sub-select not exists clause:

select id, channel temp t1 t1.lang = 'en' , not exists (   select 1   temp t2   t2.lang = 'fr'   , t1.id = t2.id ) 

alternatively, can use outer join:

select t1.id, t1.channel temp t1 left outer join temp t2 on t1.id = t2.id t1.lang = 'en' , t2.id null