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 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.
Solution:
Got the tags ids translated in english, put them into a temp table
Got the tags ids translated in spanish, put them into a temp table
Left joined the english tags, with the spanish, now I got the english that are not in the spanish
Then join the Tags table with those ids… simple… divide, and you’ll conquer…
Here’s the code:
#1 FIRST WE GET THE TAGS WE TRANSLATED IN THE MASTER LANGUAGE
#AND PUT IT IN A TEMPORARY TABLE TAGS_MASTER
DROP TEMPORARY TABLE TAGS_MASTER;
CREATE TEMPORARY TABLE TAGS_MASTER AS (SELECT DISTINCT
Trans_tag_fk_id Tag_master_id
FROM TRANSLATIONS
WHERE Trans_lang_fk_id = $master_lang_id);
#2 THEN WE GET THE TAGS WE HAVE TRANSLATED SO FAR IN THE SLAVE LANGUAGE
#TRANS_SLAVE
DROP TEMPORARY TABLE TAGS_SLAVE;
CREATE TEMPORARY TABLE TAGS_SLAVE (SELECT DISTINCT
Trans_tag_fk_id Tag_slave_id
FROM TRANSLATIONS
WHERE Trans_lang_fk_id = $slave_lang_id);
#3 WE DO A LEFT JOIN (MASTER,SLAVE) TO OBTAIN THE IDS OF
#TAGS THAT HAVE NOT BEEN TRANSLATED YET IN THE SLAVE LANGUAGE
DROP TEMPORARY TABLE TAGS_MASTER_NOT_SLAVE;
CREATE TEMPORARY TABLE TAGS_MASTER_NOT_SLAVE (SELECT Tag_master_id,
Tag_slave_id
FROM TAGS_MASTER LEFT JOIN TAGS_SLAVE
ON Tag_master_id = Tag_slave_id WHERE
Tag_slave_id IS NULL);
#4 THEN WE JOIN THE RESULTS WITH THE TAGS TABLE
#AND APPLY THE GIVEN FILTERS
SELECT Tag_pk_id, Tag_name
FROM TAGS JOIN TAGS_MASTER_NOT_SLAVE
ON Tag_pk_id = Tag_master_id;
#DROP THE TEMPORARY TABLES
DROP TEMPORARY TABLE TAGS_MASTER_NOT_SLAVE;
DROP TEMPORARY TABLE TAGS_SLAVE;
DROP TEMPORARY TABLE TAGS_MASTER;
—
Hail SQL.