David,
Firstly, to answer your question I don't know of a MySQL function that
may allow you to sort indirectly by the contents of a field - there are
many many functions and some of them are very specialised, and if you
ever move to another db all those great little functions may not be
there. However, there is a another, more standard (and IMHO better) way:
It's generally not a great idea to store lists of things in a single
field. One reason is that you've allocated n chars for the question
order, but what if a survey suddenly gets twice as many questions? - You
then need to resize your columnns or make them all huge to start with.
The other reason is that it creates problems like the one you're having
here.
To avoid lists in a field, you need to create additional tables to store
the lists, but then the problem you have of sorting goes away as if by
magic.
Here's an example for survey and question (note, I've renamed you
original SURVEY_QUESTION table to QUESTION):
create table SURVEY
( SID int primary key not null auto_increment,
SNAME varchar(20),
) ;
create table QUESTION
( QID int primary key not null auto_increment,
QBODY varchar(255),
) ;
create table SURVEY_QUESTION
( SID int , (you'd probably make these 2 fields the primary key)
QID int ,
QUESTION_ORDER int
) ;
Now you can have the same question in many surveys and the
QUESTION_ORDER field in SURVEY_QUESTION allows you to sort the questions
in any order for that survey. (You just put arbitary numbers in
QUESTION_ORDER to make the order work e.g. 10, 20, 30 - by using
multiples of 10 you can insert a question without renumbering the
order.)
I hope that helps,
Andy
David T-G wrote:
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hi, all --
>
> I have a few tables something like
>
> create table SURVEY
> ( SID int primary key not null auto_increment,
> SNAME varchar(20),
> QUESTION_ORDER varchar(20) ) ;
> create table SURVEY_QUESTION
> ( QID int primary key not null auto_increment,
> QBODY varchar(255),
> OPTION_ORDER varchar(20) ) ;
> create table SURVEY_OPTION
> ( OID int primary key not null auto_increment,
> OBODY varchar(255) ) ;
>
> and in the QUESTION_ORDER field I store a list of numbers "12 13 21 14"
> while in OPTION_ORDER it might be "432 435 435 550" or such. I want to
> do a select on all three (double left join, if I am starting to grasp
> this stuff :-) ordered first by the QID as shown in QUESTION_ORDER and
> then by the OID as shown in OPTION_ORDER.
>
> Is there a way to tell my join-and-select statement the sort order based
> on the contents of another field (but not simply sorting on that field)?
>
> TIA & HAND
>
> :-D
> - --
> David T-G * There is too much animal courage in
> (play) [EMAIL PROTECTED] * society and not sufficient moral courage.
> (work) [EMAIL PROTECTED] -- Mary Baker Eddy, "Science and Health"
> http://justpickone.org/davidtg/ Shpx gur Pbzzhavpngvbaf Qrprapl Npg!
>
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.0.7 (FreeBSD)
>
> iD8DBQE/NHTHGb7uCXufRwARAtPvAKDql3YjpBwwEpS5trzncnOzeTjXUACfZo93
> Kep54aY/EeVXaCXXlItbKl0=
> =qi7g
> -----END PGP SIGNATURE-----
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
