I wrote:
> # create test tables
> create table msg_main (mid int not null,name char(16),primary key(mid));
> create table msg_lang (mid int not null,lang char(2) not null,message
> text,primary key(mid,lang));
mid is a bad choice for column names, because it is a function... it is
however not an reserved keyword (in the version I am using, 3.23.39).
> # select best choice, order of preferred language is: 'en','se','no'
> select msg_lang.message
> from msg_main
> left join msg_lang on
> msg_lang.mid=msg_main.mid
> left join msg_lang as unwanted on
> unwanted.mid=msg_main.mid and
> field(unwanted.lang,'en','se','no') <
> field(msg_lang.lang,'en','se','no')
> where name in ('welcome','goodbye') and isnull(unwanted.mid);
This won't work... sorry!
It worked with the test data, though... ;o)
This is version 1.1:
select name,msg_lang.message
from msg_main
left join msg_lang on
msg_lang.mid=msg_main.mid and
field(msg_lang.lang,'en','se','no') > 0
left join msg_lang as unwanted on
unwanted.mid=msg_main.mid and
field(unwanted.lang,'en','se','no') > 0 and
field(unwanted.lang,'en','se','no') <
field(msg_lang.lang,'en','se','no')
where name in ('welcome','goodbye') and
msg_lang.lang in ('en','se','no') and
isnull(unwanted.mid);
I think this is closer to something that may work. Note that you need to
insert your language preferences five places.
(There has _got_ to be an easier way...? For version 3.23 that is, I know
it's easier in 4.0)
Roger Baklund,
Mobiliant AS
---------------------------------------------------------------------
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
