Hello list, I've got a query problem with MySQL 4._0_.
A specific SELECT will give me a result set with two columns, an ID and an information. Now I want to group all results by their ID. Speak: In the result there should be only one row per ID but the informations from the different rows shall be summarized within a single field. I hope that was understandable enough... Searching the manual I found GROUP_CONCAT which seems to do what I want. But this is only available for MySQL 4._1_. And for now I can't update. Is there any other possibility to get that to work? I want to keep all the logic within the SQL statement since it is just one within a huge selection of queries. A performance penalty shouldn't be a problem, since this whole query will only be called once a day at 3 or 4 AM to generate a file. Thank you Marcel PS: a simple example I will try to show it with a university database example. I have a table with all lectures, a table with all students and a table which creates a relation "hearing" between the two. I need a table where I have exactly one row for each lecture and all students within a column. SELECT hearing.lecture_id, students.name FROM hearing LEFT JOIN students ON hearing.student_id = students.id; That gives me the needed lecture/student combination (ok, I even need the lecture name instead of the id, but that's just a JOIN more). Hoehere Mathematik | Guenter Beckstein Hoehere Mathematik | Edmund Stoiber Hoehere Mathematik | Gerhard Schroeder Diskrete Strukturen | Britney Spears Diskrete Strukturen | Anastasia Diskrete Strukturen | Backstreet Boys But I still have a row for each combination. When I change the query to include a GROUP BY like this SELECT hearing.lecture_id, students.name FROM hearing LEFT JOIN students ON hearing.student_id = students.id GROUP BY hearing.lecture_id; I will get only one row per lecture (good) but also only with a single student in the name-field - the first one (bad). Hoehere Mathematik | Guenter Beckstein Diskrete Strukturen | Britney Spears instead of Hoehere Mathematik | Guenter Beckstein, Edmund Stoiber, Gerhard Schroeder Diskrete Strukturen | Britney Spears, Anastasia, Backstreet Boys which is what I need -- Marcel Meyer | Netzwerk- und Rechnerorganisation | Fachschaft Mathematik/Physik/Informatik | Technische Universität München -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
