CODEWITHSUNDEEP
mysqlsqldatabase
Zar
Zar

Reputation: 6882

Gathering multiple results in one column

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 do realize that this is not a fully normalized example, trying to keep it simple.)

Gathering data from them is pretty straight-forward:

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

However, this obviously yields one row per author - which is is not what I want. Instead, I'm trying to fulfill this data-structure:

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

Is it possible to do this in one single query? Preferably without "merging" the author columns into one single string. Something like an internal array in the column itself.

Upvotes: 4

Views: 393

Answers (3)

user166390
user166390

Reputation:

Is it possible to do this in one single query? Preferably without "merging" the author columns into one single string. Something like an internal array in the column itself.

No :)

GROUP_CONCAT and similar yields a scalar (String) result. There is no "Array of Strings" data-type in [standard] SQL.

This is "OK" because

  1. a client program can de-normalize the data itself1 for further processing, or
  2. a de-normalized string can be sent to a command-line/text client for data visualization, or
  3. the normalized form is desired for using the result with further queries2.

1 Creating an Array for further processing is generally done on the client and varies by languages - it is a trivial one-liner in C#/LINQ.

2 By leaving it in normalized form, further Relational Algebra operations/optimizations can be applied. De-normalizing the data early can eliminate a wide range of valid (and possibly more efficient) query plans.

Upvotes: 2

echo_Me
echo_Me

Reputation: 37233

try this

 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

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269483

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 the authors into a comma separated list. You can specify the separator using the SEPARATOR statement.

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

Upvotes: 1

Related Questions