Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
Answers are easier and can be more specific if you name your RDBMS and version. –  Erwin Brandstetter Oct 27 '12 at 18:42
    
You can assume SQLite (I also updated the question). In that case the aggregation function is "group_concat". –  canpolat Oct 27 '12 at 18:50
    
Updated my answer accordingly. –  Erwin Brandstetter Oct 27 '12 at 19:03
add comment

3 Answers 3

up vote 5 down vote accepted

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.

share|improve this answer
add comment

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...

share|improve this answer
    
This form is incorrect if there can be multiple persons per role. –  Erwin Brandstetter Oct 27 '12 at 19:06
    
See my update in the question. What do you mean by "incorrect"? –  canpolat Oct 27 '12 at 19:11
    
It would result in a Cartesian product instead of listing the relevant persons once per book. The author of this closely related question on SO ran into the same problem. See my explanation there. –  Erwin Brandstetter Oct 27 '12 at 19:32
add comment

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.

share|improve this answer
1  
I agree completely. If the schema was designed correctly as you suggested the query is simple. Too bad no one but me agrees with you. –  Todd Everett Oct 30 '12 at 19:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.