Many-to-Too-Many Relationship

Question

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.

---

Update

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.

Answer 1

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:

Answer 2

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:

Answer 1

If there can be more than one author / editor / translator per book - like it is in real life and like your relational design suggests, then the existing answer with plain LEFT JOINs will produce incorrect results. If any book could have at most one person for each of the roles, you could radically simplify your design: no junction tables needed, just a foreign key column in the table Books.

You need to aggregate authors, translators and editors. You could do that after joining all rows (and producing mulitple resulting rows), but it should be more efficient to aggregate in the subqueries and then join to the Books table

SELECT B.BookTitle, BA.Authors, BT.Translators, BE.Editors
FROM   tblBook B
LEFT JOIN (
    SELECT  J.BookID, group_concat(P.Name, ', ') AS Authors
    FROM    tblBookAuthorJunction J
    JOIN    tblPerson P ON J.AuthorID = P.PersonID
    GROUP   BY J.BookID
    ) AS BA ON BA.BookID = B.ID

LEFT JOIN (
    SELECT  J.BookID, group_concat(P.Name, ', ') AS Translators
    FROM    tblBookTranslatorJunction J
    JOIN    tblPerson P ON J.TranslatorID = P.PersonID
    GROUP   BY J.BookID
    ) AS BT ON BT.BookID = B.ID

LEFT JOIN (
    SELECT  J.BookID, group_concat(P.Name, ', ') AS Editors
    FROM    tblBookEditorJunction J
    JOIN    tblPerson P ON J.EditorID = P.PersonID
    GROUP   BY J.BookID
    ) AS BE ON BE.BookID = B.ID
WHERE  B.ID = 123

In most RDBMS it will be faster if you add WHERE BookID = 123 to the subqueries, too.

Answer 2

I think I finally found a way to get the output I need.

SELECT B.BookTitle, P1.PersonName, P2.PersonName, P3.PersonName
FROM tblBook B
LEFT JOIN tblBookAuthorJunction A ON B.BookID = A.BookID
LEFT JOIN tblPerson P1 ON A.AuthorID = P1.PersonID
LEFT JOIN tblBookTranslatorJunction T ON B.BookID = T.BookID
LEFT JOIN tblPerson P2 ON T.TranslatorID = P2.PersonID
LEFT JOIN tblBookEditorJunction E ON B.BookID = E.BookID
LEFT JOIN tblPerson P3 ON E.EditorID = P3.PersonID

I'm not sure if there is a faster way to get the desired result. If so, please add an answer, otherwise, I'll mark this as the answer in a day or so...

Answer 3

I'm not sure the semantics of your design holds up, and that it's the reason why you're having a hard time now.

I'd argue that the relationship is between a person and a book, and only the type of the relationship changes, so that you could have a type column on the tblAuthorPersonJunction. If you need it to be possible for each person to have multiple roles in regards to a single book, include type in you primary key, so that it's the unique combination of the three columns that identifies a row.

If you approach the problem this way, I think it'd be possible to use PIVOT to simplify the query for you. Someone who's more familiar with PIVOT syntax should be able to tell you if I'm wrong.