Sort (ORDER BY) by a comma-separated adjoined string of related separated fields in MySQL 4.1?

[phillip.s.powell]

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

[phillip.s.powell]

Probably not the best solution, but the only one awarded to me:

I wrote a "stored procedure", which in reality is a CLI PHP script that
queries the DB, returns a resultset (object array) and manipulates it
into the correct structured order (the ethnicities grouped together
into a singular object for each object in the array) before returning a
serialized object array.

Phil

[Bill Karwin]

<ph**************@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

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?

This might help:

SELECT table_a.*, GROUP_CONCAT(b.b_name) AS joined_b_names
FROM table_a INNER JOIN a_b_assoc USING (a_id)
INNER JOIN table_b USING (b_id)
GROUP BY a_id
ORDER BY joined_b_names ASC

See http://dev.mysql.com/doc/refman/5.0/...functions.html for docs
on the GROUP_CONCAT() function.

Regards,
Bill K.

[phillip.s.powell]

Sorry I tried GROUP_CONCAT, and each time I did the grouping of the
b_name field values were completely wrong, it would seem to group them
almost randomly as opposed to the correct grouping using a_id.

Phil

Bill Karwin wrote:

<ph**************@gmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com...

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?

This might help:

SELECT table_a.*, GROUP_CONCAT(b.b_name) AS joined_b_names
FROM table_a INNER JOIN a_b_assoc USING (a_id)
INNER JOIN table_b USING (b_id)
GROUP BY a_id
ORDER BY joined_b_names ASC

See http://dev.mysql.com/doc/refman/5.0/...functions.html for docs
on the GROUP_CONCAT() function.

Regards,
Bill K.

[Bill Karwin]

<ph**************@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Sorry I tried GROUP_CONCAT, and each time I did the grouping of the
b_name field values were completely wrong, it would seem to group them
almost randomly as opposed to the correct grouping using a_id.

There is an option to specify an ordering of the items concatenated.

I'm detecting with you a trend that when I give pointers to pages from the
documentation, you apparently do not read them. You really should really
follow up and supplement advice from newsgroups with some self-education.

Regards,
Bill K.

[phillip.s.powell]

NO, I do read them, but often times don't understand them, or not
enough to know how to apply them. I can't seem to convey that to
people unless they understand the effects of ADD and know how it can
hamper learning.

Phil

Bill Karwin wrote:

<ph**************@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Sorry I tried GROUP_CONCAT, and each time I did the grouping of the
b_name field values were completely wrong, it would seem to group them
almost randomly as opposed to the correct grouping using a_id.

There is an option to specify an ordering of the items concatenated.

I'm detecting with you a trend that when I give pointers to pages from the
documentation, you apparently do not read them. You really should really
follow up and supplement advice from newsgroups with some self-education.

Regards,
Bill K.

[Bill Karwin]

<ph**************@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

NO, I do read them, but often times don't understand them, or not
enough to know how to apply them. I can't seem to convey that to
people unless they understand the effects of ADD and know how it can
hamper learning.

Well, yes, that does sound like quite a difficult challenge. A programming
career is full of daily requirements to learn new tools and techniques.

The GROUP_CONCAT function allows you to specify the order of the strings
concatenated. Otherwise, the order may be dependent on how the records are
stored, which makes it effectively unpredictable.

Specifying the sorting order of strings with GROUP_CONCAT is done with
similar syntax to the order of rows in the query result set. Use an ORDER
BY clause, inside the GROUP_CONCAT function call.

For example:

SELECT a.*, GROUP_CONCAT(b.b_name ORDER BY b.b_name) AS joined_b_names
FROM table_a AS a INNER JOIN a_b_assoc USING (a_id)
INNER JOIN table_b AS b USING (b_id)
GROUP BY a.a_id
ORDER BY joined_b_names ASC

Regards,
Bill K.

[phillip.s.powell]

Ok, thanx that is a bit more clear, unfortunately, I still can't get it
right, produces a syntax error:

'INNER JOIN student_ethnicity_interest_assoc seia USING
(seia.student_id) ' .
'INNER JOIN ethnicity e USING (e.id) ' .

produces

Fatal error: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near '(seia.student_id) INNER JOIN ethnicity e USING (e.id) WHERE
MATCH(s.major, s.minor) ' at line 1

Phil
Bill Karwin wrote:

<ph**************@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

NO, I do read them, but often times don't understand them, or not
enough to know how to apply them. I can't seem to convey that to
people unless they understand the effects of ADD and know how it can
hamper learning.

Well, yes, that does sound like quite a difficult challenge. A programming
career is full of daily requirements to learn new tools and techniques.

The GROUP_CONCAT function allows you to specify the order of the strings
concatenated. Otherwise, the order may be dependent on how the records are
stored, which makes it effectively unpredictable.

Specifying the sorting order of strings with GROUP_CONCAT is done with
similar syntax to the order of rows in the query result set. Use an ORDER
BY clause, inside the GROUP_CONCAT function call.

For example:

SELECT a.*, GROUP_CONCAT(b.b_name ORDER BY b.b_name) AS joined_b_names
FROM table_a AS a INNER JOIN a_b_assoc USING (a_id)
INNER JOIN table_b AS b USING (b_id)
GROUP BY a.a_id
ORDER BY joined_b_names ASC

Regards,
Bill K.

[Bill Karwin]

<ph**************@gmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

Fatal error: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near '(seia.student_id) INNER JOIN ethnicity e USING (e.id) WHERE
MATCH(s.major, s.minor) ' at line 1

The USING clause is useful when you're joining tables and the column has an
identical name in both tables in the join. That, a column by that name has
to appear in both tables.

For example, this clause:

FROM table_a INNER JOIN table_b USING (id)

is the same as this:

FROM table_a INNER JOIN table_b ON table_a.id = table_b.id

If the columns over which you join have different names in each table, you
must use the ON syntax, and write an expression.

Regards,
Bill K.

[phillip.s.powell]

I did, and used ON instead, which worked, until the elements in
"ethnicity_name" formed by GROUP_CONCAT() changed orderly randomly, and
since I have a demo at 11am tomorrow, I didn't have time to fix that
and the search capability and the customer feedback at the same time,
so I gave up and went back to the "stored procedure" model, which works
perfectly, even if not pretty.

And being at work for 12 hours for the sixth day in a row doesn't help
either. :(

Thanx though!
Phil

Bill Karwin wrote:

<ph**************@gmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...

Fatal error: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to
use near '(seia.student_id) INNER JOIN ethnicity e USING (e.id) WHERE
MATCH(s.major, s.minor) ' at line 1

The USING clause is useful when you're joining tables and the column has an
identical name in both tables in the join. That, a column by that name has
to appear in both tables.

For example, this clause:

FROM table_a INNER JOIN table_b USING (id)

is the same as this:

FROM table_a INNER JOIN table_b ON table_a.id = table_b.id

If the columns over which you join have different names in each table, you
must use the ON syntax, and write an expression.

Regards,
Bill K.