I'm working on a book database. And among others, I have the following two tables (a bit simplified for the question):
tblBook: ID, Title tblPerson: ID, Name
Note that the
tblPerson table contains authors, translators and editors (not only authors, that is).
Obviously, there is a many-to-many relationship between these two tables, so I also have the following junction tables:
tblBookAuthorJunction tblBookTranslatorJunction tblBookEditorJunction
But when it comes to joining the tables to select all the roles, I have a problem: how do I create a query to have a table that looks like:
BookTitle AuthorName TranslatorName EditorName
Is this at all possible? What should my query look like?
Edit: You can assume SQLite for the RDBMS.
So far, there are two answers that contain queries. I would like to compare these answers for others who may find this information valuable... Note that the names of the junction tables are slightly different than the ones in the question. And, yes I know, the test data is boring.
The query in my own answer is as follows:
SELECT B.BookTitle, P1.PersonName, P2.PersonName, P3.PersonName FROM tblBook B LEFT JOIN tblBookAuthor A ON B.BookID = A.BookID LEFT JOIN tblPerson P1 ON A.AuthorID = P1.PersonID LEFT JOIN tblBookTranslator T ON B.BookID = T.BookID LEFT JOIN tblPerson P2 ON T.TranslatorID = P2.PersonID LEFT JOIN tblBookEditor E ON B.BookID = E.BookID LEFT JOIN tblPerson P3 ON E.EditorID = P3.PersonID
And the output of this query looks like this:
The query in Erwin Brandstetter's answer is as follows (little bit modified to fit the test database):
SELECT B.BookTitle, BA.Authors, BT.Translators, BE.Editors FROM tblBook B LEFT JOIN ( SELECT J.BookID, group_concat(P.PersonName, ', ') AS Authors FROM tblBookAuthor J JOIN tblPerson P ON J.AuthorID = P.PersonID GROUP BY J.BookID ) AS BA ON BA.BookID = B.BookID LEFT JOIN ( SELECT J.BookID, group_concat(P.PersonName, ', ') AS Translators FROM tblBookTranslator J JOIN tblPerson P ON J.TranslatorID = P.PersonID GROUP BY J.BookID ) AS BT ON BT.BookID = B.BookID LEFT JOIN ( SELECT J.BookID, group_concat(P.PersonName, ', ') AS Editors FROM tblBookEditor J JOIN tblPerson P ON J.EditorID = P.PersonID GROUP BY J.BookID ) AS BE ON BE.BookID = B.BookID
The output of this query looks like this: