Collecting multiple results in one column

Question

I have two tables:

╔══════════════╦═════════════╗ ║ Table: book ║ Column info ║ ╠══════════════╬═════════════╣ ║ - id ║ int, pk ║ ║ - title (128)║ varchar ║ ╚══════════════╩═════════════╝ ╔══════════════╦═════════════╗ ║Table: author ║ Column info ║ ╠══════════════╬═════════════╣ ║ - id ║ int, pk ║ ║ - name (128) ║ varchar ║ ║ - bookId ║ int, fk ║ ╚══════════════╩═════════════╝ 

(I really understand that this is not a completely normalized example, trying to keep it simple.)

The data collection from them is pretty straightforward:

 SELECT b.title, a.name FROM book b INNER JOIN author a ON a.bookId = b.id WHERE b.id = x 

However, this obviously gives one line per author - this is not what I want. Instead, I try to execute this data structure:

[String title, String [] authors (array)]

Is it possible to do this in one request? Preferably without “combining” the author’s columns into a single row. Something like an internal array in the column itself.

Answer 1

Is it possible to do this in one query? Preferably without “combining” the author’s columns into a single row. Something like an internal array in the column itself.

No:)

GROUP_CONCAT and similar results give a scalar (String) result. In the [standard] SQL type, there is no "Array of rows" data type.

This is OK because

• the client program can de-normalize the data ^{1 itself} for further processing or
• the de-normalized string can be sent to the command line client / text client to visualize the data or
• a normalized form is required to use the result with further queries ² .

---

¹ Creating an array for further processing is usually performed on the client and depends on the languages ​​- this is a trivial single-line layer in C # / LINQ.

² Leaving it in a normalized form, you can apply the Relational algebra of operations / optimizations. De-normalizing data early can eliminate a wide range of valid (and possibly more efficient) query plans.

Answer 2

Do you want group_concat :

 select b.title, group_concat(a.name) from book b join author a on b.id = a.bookid group by b.id, b.title 

This puts all authors on a comma-separated list. You can specify a delimiter using the SEPARATOR .

Note. I also grouped the book ID, in case two books have the same title.

Answer 3

try it

  SELECT b.title,a.name, concat('[',b.title,',',a.name, ']') as output FROM book b INNER JOIN author a ON b.id = a.bookid GROUP BY b.id, b.title 

SQL DEMO