Base 5: How do I combine query result rows that differ in just one field?

[Brian_McGuinness]

I have created a database to keep track of my books. I have a table for the books (ID #, title, date published, ISBN, etc.), a table of people (ID #, name, etc. for authors and editors), and a table with one column for book ID numbers and one column for author ID numbers. If a book has more than one author it will have multiple entries in the third table.

When I do a query to display book information, including authors, for each book that has more than one author I get multiple rows in the result, one for each author. I would like to have just one row per book in the result, with all of the authors merged into one field. Separating the names with commas and blanks would be fine (e.g. “Jerry Pournelle, Larry Niven”).

Please tell me how to do this. I would prefer sticking to standard SQL if possible, but if this can’t be done then anything that works would be ok.

Thank you.

[EasyTrieve]

Updated: I have done this in MS Access w/ VBA. You can write Basic to do it. In the basic event you need to lookup the authors, get the results, and concatenate them together and then stuff them in a field. It is relatively easy once you understand the macro object lay of the land, but at the same time it is much harder than it looks due to the steep learning curve to write the Basic involved. I’ll keep your Q in mind. If I ever write this again in LO I’ll try to share it here.

[frofa]

There is an SQL Function called GROUP_CONCAT which should do the job. NOTE: This function is not available in HSQLDB version 1.8 (the default engine for ‘embedded’ Base databases), but is available in HSQLDB v.3.x which you can use if you convert your database to a SPLIT database setup. It’s also available in MYSQL.

[EasyTrieve]

Interesting. Thanks. ref manual is here p 125 … not sure how to create the Array.