CODEWITHSUNDEEP
mysqlsqldatabase
HerrK
HerrK

Reputation: 53

First book database problem : displaying multiple authors AND multiple book themes (but the themes get duplicated)

So I'm beginning with SQL (some Code Academy knowledge and some google searching) and I am currently building my own book database with MySQL to practice.

My problem is that, when a book has multiple authors and multiple themes, my query returns the number of authors mulitplied by the number of themes, and the number of themes multiplied by the number of authors.

Relevant to the problem, I have the following tables with the relevant columns only (it is simple so I'm displaying it with text only, every '%id' column being INTEGER) :

Tables (columns) :

I'd like my query to return this table :

id title author themes
1 title1 N1 1
2 title2 N2 2
3 title3 N2, N3 2, 3
4 title4 N4, N5, N6 2, 3

So I tried this code, which pattern I found here :

SELECT e.id, 
    e.title, 
    GROUP_CONCAT(full_name) AS author,
    GROUP_CONCAT(theme_name) AS themes
FROM ebooks e
JOIN ebooks_authors ea
     ON e.id = ea.ebook_id
JOIN authors a
     ON a.id = ea.author_id
JOIN ebooks_themes et
     ON e.id = et.ebook_id
JOIN theme t
     ON t.id = et.theme_id
GROUP BY e.id;

If I'm not adding the parts where themes are involved, I have the right number of authors for each row, but with the theme-related code added, I have something such as :

Book_id Title Author Themes
1 title1 N1 1
2 title2 N2 2
3 title3 N2, N3, N2, N3 2, 3, 2, 3
4 title4 N4, N4, N5, N5, N6, N6 2, 2, 2, 3, 3, 3

I tried to GROUP BY both e.id and t.id, but it creates a row for each theme, and replicates the theme_name where there were multiple authors.

I don't really know what kind of function I'm searching for, or if I could obtain what I want with a WITH clause or a subquery.

Could someone help me to obtain the first table in this post ?

(I'm sorry if that was too long of a description, but) thank you for your attention !

Upvotes: 2

Views: 121

Answers (1)

nbk
nbk

Reputation: 49375

you can add a DISTINCT and also a ORDER BY to GROUP_CONCAT

SELECT e.id, 
    e.title, 
    GROUP_CONCAT(DISTINCT full_name) AS author,
    GROUP_CONCAT(DISTINCT theme_name) AS themes
FROM ebooks e
JOIN ebooks_authors ea
     ON e.id = ea.ebook_id
JOIN authors a
     ON a.id = ea.author_id
JOIN ebooks_themes et
     ON e.id = et.ebook_id
JOIN theme t
     ON t.id = et.theme_id
GROUP BY e.id;

Upvotes: 1

Related Questions