create table table_a (
id int not null,
primary key (id),
a_name varchar(125) not null
);
create table table_b (
id int not null,
primary key (id),
b_name varchar(75) not null
);
create table a_b_assoc (
a_id int not null,
b_id int not null,
primary key (a, b)
);
Ok, you have three tables. You're supposed to be able to not only sort (ORDER BY) according to a_name, no problem, but you must also have the ability to sort (ORDER BY) the relationship between table_a and table_b, that is, say you have this:
insert into table_a (a_name) values ('Phil');
insert into table_b (b_name) values ('programmer');
insert into table_b (b_name) values ('developer');
insert into table_c (c_name) values ('emperor of the known universe');
insert into a_b_assoc (a_id, b_id) values ('1', '1');
insert into a_b_assoc(a_id, b_id) values ('1', '2');
insert into a_b_assoc(a_id, b_id) values ('1', '3');
Here is where the problem lies. I am required to be able to sort by 'Phil', no problem:
ORDER BY upper(a_name) ASC
But how on earth do I sort THIS way:
--PSEUDO CODE, of course
ORDER BY upper('programmer, developer, emperor of the known universe') ASC
in other words, how do I sort a resultset query where each a_name will have multiple b_name field values BY their adjoined b_name field values comma-separated?
I'm sorry if that made no sense but that's as clear as I'm able to make it.
Phil
