How to use two many to many associations to retrieve all associations in a single SQL select statment?

Question

I have a books table that has a many-to-many association with a genre table and an authors table. The aim is to pull out all relevent information about a book in a single select statment.

I am using MariaDB 10.11 and the fiddle is in 10.9

Below is the code to create and fill the tables with example data, I have also linked to a dbfiddle

CREATE TABLE book (id int AUTO_INCREMENT PRIMARY KEY, title varchar(30));
CREATE TABLE author_example (id int AUTO_INCREMENT PRIMARY KEY, name varchar(50));
CREATE TABLE genre (id int AUTO_INCREMENT PRIMARY KEY, genre varchar(50));
CREATE TABLE associate_book_genre (
    genre_id int NOT NULL,
    book_id int NOT NULL,
    CONSTRAINT pk_book_genre PRIMARY KEY (book_id, genre_id)
  );
CREATE TABLE associate_book_author (
    author_id int NOT NULL,
    book_id int NOT NULL,
    CONSTRAINT pk_book_genre PRIMARY KEY (book_id, author_id)
    );

INSERT INTO genre (genre) VALUES ("travel"),("philosophy"),("nonfiction");
INSERT INTO author_example (name) VALUES ("Paul Tillich"),("Douglas Adams"),("Mark Carardine");
INSERT INTO book (title) VALUES ("The Courage to Be"), ("Last Chance to See"),("Beowulf"),("Debate between bird and fish");

https://dbfiddle.uk/74kXDrRM

My aim is to retrieve this table:

Book title	Authors	Genres
Beowulf	null	null
Last Chance to See	Douglas Adams & Mark Carardine	travel, nonfiction
The Courage to Be	Paul Tillich	philosophy
Debate between bird and fish	null	null

The closest I have come is with

SELECT b.title,
  GROUP_CONCAT(a.name SEPARATOR " & ") as author, 
  GROUP_CONCAT(g.genre SEPARATOR ", ") as genre FROM book b 
left join associate_book_author aba on b.id = aba.book_id 
left join author_example a on aba.author_id=a.id 
left join associate_book_genre abg on b.id = abg.book_id 
left join genre g on abg.genre_id =g.id
  group by g.id;

Book title	Authors	Genres
Beowulf	null	null
Last Chance to See	Douglas Adams & Mark Carardine	travel, travel
The Courage to Be	Paul Tillich	philosophy
Last Chance to See	Douglas Adams & Mark Carardine	nonfiction, nonfiction

However I am missing "Debate between bird and fish" since the genre is null for both "Debate between bird and fish" and "Beowulf" and "Last Chances to See" is listed twice and the genres are wrong.

Answer 1

You should individually aggregate the authors and genres:

with a as (
 select aa.book_id, GROUP_CONCAT(a.name SEPARATOR " & ") as Authors
 from associate_book_author aa
 join author_example a on a.id = aa.author_id
 group by aa.book_id
), g as (
 select ag.book_id, GROUP_CONCAT(g.genre SEPARATOR ", ") as Genres
 from associate_book_genre ag
 join genre g on g.id = ag.genre_id
 group by ag.book_id
)
select b.title, a.Authors, g.Genres
from book b
left join a on a.book_id = b.id
left join g on g.book_id = b.id
order by b.title;

Amended Fiddle