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

[quote]
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)
);
[/quote]

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:

[quote]
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');

[/quote]

Here is where the problem lies. I am required to be able to sort by
'Phil', no problem:

[quote]
ORDER BY upper(a_name) ASC
[/quote]

But how on earth do I sort THIS way:

[quote]
--PSEUDO CODE, of course
ORDER BY upper('programmer, developer, emperor of the known universe')
ASC
[/quote]

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

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

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

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

wrote in message
news:1139099232.794469.197480@g43g2000cwa.googlegroups.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/en/group-by-functions.ht ml for docs
on the GROUP_CONCAT() function.

Regards,
Bill K.

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

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:
> wrote in message
> news:1139099232.794469.197480@g43g2000cwa.googlegroups.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/en/group-by-functions.ht ml for docs
> on the GROUP_CONCAT() function.
>
> Regards,
> Bill K.

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

wrote in message
news:1139215240.957346.251660@o13g2000cwo.googlegroups.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.

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

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:
> wrote in message
> news:1139215240.957346.251660@o13g2000cwo.googlegroups.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.

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

wrote in message
news:1139253446.887985.233740@g44g2000cwa.googlegroups.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.

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

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:
> wrote in message
> news:1139253446.887985.233740@g44g2000cwa.googlegroups.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.

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

wrote in message
news:1139265160.920974.81490@f14g2000cwb.googlegroups.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.

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

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:
> wrote in message
> news:1139265160.920974.81490@f14g2000cwb.googlegroups.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.