Many-to-Too-Many Relationship

aggregatejoin;sqlite

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:
enter image description here

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:
enter image description here

Best Answer

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.

Related Question