select
b.id as id, concat_ws('-', substr(b.isbn,1,3), substr(b.isbn,4)) as isbn, b.name as name, bas.authors as authors, b.classification as classification, bgs.genres as genres, b.published as published
from
books b
left join (
select
ba.book_id, group_concat( concat_ws(' ', a.first_name, a.middle_name, a.last_name ) separator ', ' ) as authors
from
book_authors ba
join authors a on ba.author_id = a.id
group by
ba.book_id) as bas on b.id = bas.book_id
left join (
select
bg.book_id, group_concat(g.name separator ', ') as genres
from
book_genres bg
join genres g on bg.genre_id = g.id
group by
bg.book_id) as bgs on b.id = bgs.book_id
order by
1 asc
limit
100 offset 0
;
|
select
count(*) as count
from
books b
;
|