Mysql join same table where not in, MySQL

Mysql join same table where not in

#MySQL 5.7.12
#please drop objects you've created at the end of the script 
#or check for their existance before creating
#'\\' is a delimiter

DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table (id int, original varchar(55), alias varchar(55), lang varchar(55));
INSERT INTO my_table (id, original, alias, lang)
VALUES
    (1, 'word1es', 'word1', 'es'),
    (2, 'word1en', 'word1', 'en'),
    (3, 'word2es', 'word2', 'es'),
    (4, 'word2fr', 'word2', 'fr'),
    (4, 'word3es', 'word3', 'es');

SELECT esWords.*, langs.lang 
FROM my_table AS esWords
/* join with all other language identifiers to simulate a 
   "what is it in this language?" question
*/
LEFT JOIN (SELECT DISTINCT lang FROM my_table) AS langs 
   ON esWords.lang <> langs.lang
/* left join with my_table again to find the words for other languages */
LEFT JOIN my_table AS otherWords 
   ON esWords.alias = otherWords.alias
   AND langs.lang = otherWords.lang
WHERE esWords.lang = 'es' 
   AND otherWords.id IS NULL /* filter out all languages where a 
                                word was found for that language
                              */


run \| edit \| history \| help	0