Today I spent a great deal of time trying to pull something in one fancy SQL statement.<\/p>\n
In the end, the words of my first algorithm teacher and common sense made me desist from making such a fancy SQL statement, which I still believe can be made…<\/p>\n
The problem:<\/p>\n
I have a bunch of Generic Translation Tags. These tags have been translated to english, and they need to be translated to other languages. I want to make a querie which will tell me, which Tags have not been translated yet to the other language… so you would think its only a left join and that’s it… think again.<\/p>\n
Solution:
\nGot the tags ids translated in english, put them into a temp table
\nGot the tags ids translated in spanish, put them into a temp table
\nLeft joined the english tags, with the spanish, now I got the english that are not in the spanish
\nThen join the Tags table with those ids… simple… divide, and you’ll conquer…<\/p>\n
Here’s the code:
\n#1 FIRST WE GET THE TAGS WE TRANSLATED IN THE MASTER LANGUAGE
\n#AND PUT IT IN A TEMPORARY TABLE TAGS_MASTER
\nDROP TEMPORARY TABLE TAGS_MASTER;
\nCREATE TEMPORARY TABLE TAGS_MASTER AS (SELECT DISTINCT
\nTrans_tag_fk_id Tag_master_id
\nFROM TRANSLATIONS
\nWHERE Trans_lang_fk_id = $master_lang_id);<\/p>\n
#2 THEN WE GET THE TAGS WE HAVE TRANSLATED SO FAR IN THE SLAVE LANGUAGE
\n#TRANS_SLAVE
\nDROP TEMPORARY TABLE TAGS_SLAVE;
\nCREATE TEMPORARY TABLE TAGS_SLAVE (SELECT DISTINCT
\nTrans_tag_fk_id Tag_slave_id
\nFROM TRANSLATIONS
\nWHERE Trans_lang_fk_id = $slave_lang_id);<\/p>\n
#3 WE DO A LEFT JOIN (MASTER,SLAVE) TO OBTAIN THE IDS OF
\n#TAGS THAT HAVE NOT BEEN TRANSLATED YET IN THE SLAVE LANGUAGE
\nDROP TEMPORARY TABLE TAGS_MASTER_NOT_SLAVE;
\nCREATE TEMPORARY TABLE TAGS_MASTER_NOT_SLAVE (SELECT Tag_master_id,
\nTag_slave_id
\nFROM TAGS_MASTER LEFT JOIN TAGS_SLAVE
\nON Tag_master_id = Tag_slave_id WHERE
\nTag_slave_id IS NULL);<\/p>\n
#4 THEN WE JOIN THE RESULTS WITH THE TAGS TABLE
\n#AND APPLY THE GIVEN FILTERS
\nSELECT Tag_pk_id, Tag_name
\nFROM TAGS JOIN TAGS_MASTER_NOT_SLAVE
\nON Tag_pk_id = Tag_master_id;<\/p>\n
#DROP THE TEMPORARY TABLES
\nDROP TEMPORARY TABLE TAGS_MASTER_NOT_SLAVE;
\nDROP TEMPORARY TABLE TAGS_SLAVE;
\nDROP TEMPORARY TABLE TAGS_MASTER;
\n—<\/p>\n
Hail SQL.<\/p>\n","protected":false},"excerpt":{"rendered":"
Today I spent a great deal of time trying to pull something in one fancy SQL statement. In the end, the words of my first algorithm teacher and common sense made me desist from making such a fancy SQL statement, which I still believe can be made… The problem: I have a bunch of Generic […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[33],"tags":[],"class_list":["post-172","post","type-post","status-publish","format-standard","hentry","category-gubatron"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p5Unzf-2M","jetpack-related-posts":[{"id":516,"url":"https:\/\/www.gubatron.com\/blog\/ejemplo-de-automatizacion-entre-2-maquinas-remotas-con-bash-scripting-y-python\/","url_meta":{"origin":172,"position":0},"title":"Ejemplo de automatizacion entre 2 maquinas remotas con bash scripting y Python","author":"gubatron","date":"May 10, 2007","format":false,"excerpt":"Para los amigos que se inician en el mundo *nix, ya sea con su nueva Mac, o con una PC corriendo Linux, les recomiendo que aprendan a manejar bien los siguientes lenguajes, y el mundo sera suyo: - bash scripting (aliases, variables, exports, iteraciones, condicionales) - python (para programar logica\u2026","rel":"","context":"In "Code"","block_context":{"text":"Code","link":"https:\/\/www.gubatron.com\/blog\/category\/code\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":3739,"url":"https:\/\/www.gubatron.com\/blog\/introducing-yuca-a-light-weight-in-memory-fast-and-simple-to-use-search-engine-library\/","url_meta":{"origin":172,"position":1},"title":"Introducing Yuca: A light-weight, in-memory, fast and simple to use search engine library.","author":"gubatron","date":"May 9, 2018","format":false,"excerpt":"https:\/\/github.com\/gubatron\/yuca If your app can't handle or doesn't really need installing a full featured and heavy search engine like Lucene, nor you want to depend on a SQL database for indexing and doing simple search based strings you can use Yuca to index documents under any number of arbitrary keys\u2026","rel":"","context":"In "Code"","block_context":{"text":"Code","link":"https:\/\/www.gubatron.com\/blog\/category\/code\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.gubatron.com\/blog\/wp-content\/uploads\/2018\/05\/yuca.jpg?fit=872%2C505&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.gubatron.com\/blog\/wp-content\/uploads\/2018\/05\/yuca.jpg?fit=872%2C505&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/www.gubatron.com\/blog\/wp-content\/uploads\/2018\/05\/yuca.jpg?fit=872%2C505&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/www.gubatron.com\/blog\/wp-content\/uploads\/2018\/05\/yuca.jpg?fit=872%2C505&ssl=1&resize=700%2C400 2x"},"classes":[]},{"id":337,"url":"https:\/\/www.gubatron.com\/blog\/porque-el-podcast-de-esta-semana-se-ha-tardado\/","url_meta":{"origin":172,"position":2},"title":"Porque el podcast de esta semana se ha tardado.","author":"gubatron","date":"July 9, 2006","format":false,"excerpt":"Si existe un ser humano que esta haciendole seguimiento al proyecto SnowRSS, aqui puede ver los cambios realizados desde anoche. - Agregamos una tabla para almacenar informacion relacionada a los enclosures de los posts RSS. Es decir, soportaremos videocasts y podcasts. - Acomodamos de manera radical (refactoring) la forma en\u2026","rel":"","context":"In "Code"","block_context":{"text":"Code","link":"https:\/\/www.gubatron.com\/blog\/category\/code\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":3970,"url":"https:\/\/www.gubatron.com\/blog\/removing-duplicate-records-from-a-table-in-sqlite3-when-delete-doesnt-support-limit-1\/","url_meta":{"origin":172,"position":3},"title":"Removing duplicate records from a table in Sqlite3 when “DELETE” doesn’t support “LIMIT 1”","author":"gubatron","date":"March 6, 2022","format":false,"excerpt":"","rel":"","context":"In "Code"","block_context":{"text":"Code","link":"https:\/\/www.gubatron.com\/blog\/category\/code\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]},{"id":4129,"url":"https:\/\/www.gubatron.com\/blog\/introducing-uninews-a-universal-news-scraper-in-rust\/","url_meta":{"origin":172,"position":4},"title":"Introducing Uninews: A Universal News Scraper in Rust","author":"gubatron","date":"February 17, 2025","format":false,"excerpt":"The internet is overflowing with news, but extracting clean, readable content from articles can be a tedious task. Whether you're aggregating news for personal consumption, research, or AI training, automating this process is a must. Enter Uninews, a powerful, lightweight, and efficient Rust-based news scraper that simplifies content extraction and\u2026","rel":"","context":"In "Code"","block_context":{"text":"Code","link":"https:\/\/www.gubatron.com\/blog\/category\/code\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.gubatron.com\/blog\/wp-content\/uploads\/2025\/02\/gubatron_Logo_for_Uninews_a_universal_news_scraper_command_li_4f376071-18e3-400e-9644-8efc878465e4_3.png?fit=1024%2C1024&ssl=1&resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.gubatron.com\/blog\/wp-content\/uploads\/2025\/02\/gubatron_Logo_for_Uninews_a_universal_news_scraper_command_li_4f376071-18e3-400e-9644-8efc878465e4_3.png?fit=1024%2C1024&ssl=1&resize=350%2C200 1x, https:\/\/i0.wp.com\/www.gubatron.com\/blog\/wp-content\/uploads\/2025\/02\/gubatron_Logo_for_Uninews_a_universal_news_scraper_command_li_4f376071-18e3-400e-9644-8efc878465e4_3.png?fit=1024%2C1024&ssl=1&resize=525%2C300 1.5x, https:\/\/i0.wp.com\/www.gubatron.com\/blog\/wp-content\/uploads\/2025\/02\/gubatron_Logo_for_Uninews_a_universal_news_scraper_command_li_4f376071-18e3-400e-9644-8efc878465e4_3.png?fit=1024%2C1024&ssl=1&resize=700%2C400 2x"},"classes":[]},{"id":159,"url":"https:\/\/www.gubatron.com\/blog\/wedoit4youcom-is-back\/","url_meta":{"origin":172,"position":5},"title":"wedoit4you.com is back.","author":"gubatron","date":"May 25, 2005","format":false,"excerpt":"Finalmente voy a poder dormir tranquilo (cualquiera cae que no duermo) wedoit4you.com esta montado en el nuevo servidor. Ahora tenemos practicamente ancho de banda ilimitado y absoluto control sobre el servidor, somos root. Haciendo valer un poco la cuna, vamos a dar ahora hosting con todas las de la ley.\u2026","rel":"","context":"In "Gubatron"","block_context":{"text":"Gubatron","link":"https:\/\/www.gubatron.com\/blog\/category\/gubatron\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"_links":{"self":[{"href":"https:\/\/www.gubatron.com\/blog\/wp-json\/wp\/v2\/posts\/172","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.gubatron.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.gubatron.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.gubatron.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.gubatron.com\/blog\/wp-json\/wp\/v2\/comments?post=172"}],"version-history":[{"count":0,"href":"https:\/\/www.gubatron.com\/blog\/wp-json\/wp\/v2\/posts\/172\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.gubatron.com\/blog\/wp-json\/wp\/v2\/media?parent=172"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.gubatron.com\/blog\/wp-json\/wp\/v2\/categories?post=172"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.gubatron.com\/blog\/wp-json\/wp\/v2\/tags?post=172"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}