Topic awaiting preservation: Trickiest MySQL Challenge Yet...

Well, I appreciate all the help with my previous questions. Now I am just asking a question as a challenge to all of you. For simplicity's sake, examine this table structure modeling a many-to-many relationship:

table1

table1_id INT PRIMARY KEY
name TEXT

junction

table1_id INT
table2_id INT

table2

table2_id INT PRIMARY KEY
name TEXT

Now, what we want to produce is 2 fields, the first field is table1.name, the 2nd is the concatenation of all the table2.name fields associated with the given table1.name. Confused?

The query:
"SELECT table1.name, table2.name FROM table1, junction, table2 WHERE table1.table1_id=junction.table1_id AND junction.table2_id=table2.table2_id ORDER BY table1.name"
will produce all information you want, but with a row for every table2.name. The result is that table1.name repeats as many times as it has table2 entries associated with it.

Modifying the query to:
"SELECT table1.name, table2.name FROM table1, junction, table2 WHERE table1.table1_id=junction.table1_id AND junction.table2_id=table2.table2_id GROUP BY table1.name"
Will only produce one row for each table1.name, but the 2nd field lists only the FIRST table2.name.

To achieve the necessary results, a GROUPWISE function similar to PHP's implode() would do nicely. Unfortunately MySQL seems to only have arithmetic groupwise functions. The CONCAT() function isn't groupwise, and there appears to be no IMPLODE() style function.

Does anyone know any trick to make this work without resorting to the application layer? Does anyone even understand what I'm getting at?




-jiblet

Perhaps DISTINCT in your mySQL?

Emps

The rows are already distinct. It's either a problem of the information spread across too many rows or not enough information. Here's my post from the devshed forums, it is a bit simpler to understand I think:

Okay, suppose you have a query that outputs something like this

itemName value
item1 5
item1 6
item1 8
item2 9
item2 4
item3 9
item3 10

If you add 'GROUP BY itemName' you get

item1 5
item2 9
item3 9

If you SELECT SUM(value) rather than just value you get

item1 19
item2 13
item3 19

It seems like the groupwise functions are only arithmetic. But what if you wanted something in the vein of PHP's implode() function. I want output like this:

item1 5:6:8
item2 9:4
item3 9:10

Is this possible? Will I have to resort to using the application layer and adding looped queries?

-jiblet

The poster has demanded we remove all his contributions, less he takes legal action.
We have done so.
Now Tyberius Prime expects him to start complaining that we removed his 'free speech' since this message will replace all of his posts, past and future.
Don't follow his example - seek real life help first.

jiblet: Have I missed the point - you have tried SELECT DISTINCT etc. and it didn't work? I've had similar problems and this sorted it out no problems. If you have tried it then its is different problem or I'm thinking of a different function.

Emps

Well, the ACTUAL query SELECTS 20 fields from 9 tables with 7-15 where clauses (dynamically generated), but yes it uses SELECT DISTINCT. It's far too complicated to ask anyone to debug it, so I posted a simple case so that I could integrate the solution myself (if it is possible).

All SELECT DISTINCT does is make sure that it doesn't give u duplicate rows. The problem I am having is that if I want to get all the values associated with a given value in the first table, the only way to do so is with a unique row for each 2nd-field value. The obvious solution is GROUPing by the first field, but that causes the loss of all but one of the values in the second field. As my DevShed forum post shows, there are ways to combine these GROUPed fields arithmetically, but what i need is a String-based concatenation, preferably with my choice of a character to separate the results.

Probably the reason you misunderstood is because the DISTINCT thing solves a much more serious problem, while my problem is easy to deal with in the application code using Ini's solution.



-jiblet