Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
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
restaurants_menu
ITM_14210069
practicee
Comparación de Collations general-spanish-latin en MySQL
pets
dd
barbato
DateAdd y AddDate
Srinivas
Aula1