A combination of many sides of a many-to-one relationship in one field?
Two tables: books
and authors
. A book may have many authors, as is the case with an anthology of short stories.
books
id | title
--------------------
1 The Time Machine Did It
2 Snakes in Suits
3 Ghost in the Wires
Authors
id | name
------------------------
1 Stephen King
2 John Swartzwelder
3 Robert D. Hare
4 Kevin Mitnick
5 William L. Simon
6 Steve Wozniak
7 Paul Babiak
books_authors_link
book_id | authors_id
---------------------------
1 2
2 3
2 7
3 4
3 5
3 6
What I would like to do is return a table where each row is a book and the "authors" field is a comma separated list of all authors, like
query results
title | authors
---------------------------------
The Time Machine Did It John Swartzwelder
Snakes in Suits Robert D. Hare, Paul Babiak
Ghost in the Wires Kevin Mitnick, William L. Simon, Steve Wozniak
I've been playing GROUP BY
around with the and concatenation functions and I know the answer is there somewhere, but I just can't figure it out. I can do this by juggling arrays and string concatenations in my program itself, but this is not the elegant solution I'm looking for. Advice would be appreciated.
I am using SQLite 3 if it is system dependent.
+3
source to share