* Stig Nørgaard Jepsen > It's a miracle! Thank you so much. I didn't think it was possible. > It seems to work alright.
:) The LEFT JOIN and IF() combination is quite powerfull. > But i really don't understand what happens. > I would be very glad if you could explain for me what's the > principle in this query, so that I can make similar queries > myself when needed. Ok, I will, but I CC it to the mailinglist, maybe others have interest and/or comments. Besides, I think I may have found a bug... The basic idea is to first select all the different textkey values without considering the languageid, and then LEFT JOIN the same table two more times, one for '$Primlanguage' and one for '$Seclanguage'. LEFT JOIN is used to make sure we retrieve a row in the result set even if there is no matching row for that particular textkey/languageid combination. > > select distinct > > t1.textkey, I suppose this part is clear, we select DISTINCT t1.textkey because we only want one row for each textkey. > > if(t2.textid,t2.languageid,t3.languageid) as languageid, > > if(t2.textid,t2.textid,t3.textid) as textid, > > if(t2.textid,t2.textvalue,t3.textvalue) as textvalue The if() function takes three arguments: a condition, a true-expression and a false-expression. If the condition is true, the true-expression is returned, otherwise the false-expression is returned. NULL is considered to be false, if the t2.textid field is NULL, no t2 row was found, and the t3 value in the false-expression is used. Note that the above use of a key field in the condition is not always safe: if there exists a row with textid=0 and languageid='$Primlanguage', this textid would be considered false, and you would get the wrong result. It is safer to use "... if(!isnull(t2.textid),...". (0 is false, all other integers are true. Similar with strings: the empty string '' is false, any string value _except_ string values evaluating to 0 is true... '0' is false! With float it's quite weird...[*]) > > from texts t1 We select FROM the texts table, and alias it as 't1'. > > left join texts t2 on > > t2.textkey=t1.textkey and > > t2.languageid='da' We LEFT JOIN the 'da' rows... > > left join texts t3 on > > t3.textkey=t1.textkey and > > t3.languageid='en'; ... and the 'en' rows. Read about the LEFT JOIN here: <URL: http://www.mysql.com/doc/J/O/JOIN.html > -- Roger [*] It seems that a float value is considered to be true if the absolute value is >= 0.5, but in my implementeation, version 3.23.30-gamma, it seems that all float constants in the expression are evaluated, not the expression result: mysql> select -> if(0.5,'true','false') as '0.5', -> if(0.45+0.45,'true','false') as '0.45+0.45', -> if(0.45+0.05-0.5,'true','false') as '0.45+0.05-0.5'; +------+-----------+---------------+ | 0.5 | 0.45+0.45 | 0.45+0.05-0.5 | +------+-----------+---------------+ | true | false | true | +------+-----------+---------------+ 1 row in set (0.00 sec) I don't have a newer version of mysql available, I don't know if this is fixed, if it is intentional, or if it is a bug... I have read the documentation, <URL: http://www.mysql.com/doc/C/o/Control_flow_functions.html > mentions that "... IF(0.1) returns 0 because 0.1 is converted to an integer value, resulting in a test of IF(0).", but nothing about float expressions. Is it a bug? I think it is... --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
