MySQL GROUP_CONCAT() produces partial answer after a LIKE constraint in query

Question

select b.book_id,isbn,title,description,book_count,group_concat(concat_ws(' ',a.firstname,a.middlename,a.lastname)) as authors 
from book b, author a, author_book ab 
where b.book_id = ab.book_id and a.author_id = ab.author_id 
and (a.firstname like '%abc%') group by isbn;

The above code produces,

1001 | 1234567890123 | C Programming | Good book for beginners |         10 | abcd

but the correct answer is of multiple authors as, (including efgh, ijkl authors)

1001 | 1234567890123 | C Programming | Good book for beginners |         10 | abcd, efgh, ijkl
1002 | 1234567890111 | Java | Good book for Java Programmers |          5 | xyz, uvw
...
...

which is achieved before without using LIKE constraint as,

select b.book_id,isbn,title,description,book_count,group_concat(concat_ws(' ',a.firstname,a.middlename,a.lastname)) as authors 
from book b, author a, author_book ab 
where b.book_id = ab.book_id and a.author_id = ab.author_id group by isbn;

How to achieve the expected output?

Answer 1

The other authors are removed because your constraint on author names apply even after you have used them to select which books you want.

Use your current author filter to return a list of book ids, and then use that as a subquery WHERE bookId IN (SELECT ...) to filter which books you want, and join again with author_book and author to get all authors of selected books.

Try something like this:

select  b.book_id,isbn,title,description,book_count,
 group_concat(concat_ws(' ',a.firstname,a.middlename,a.lastname)) as authors 
from book b, author a, author_book ab 
where 
 b.book_id = ab.book_id
 and a.author_id = ab.author_id 
 and b.book_id IN (
  SELECT ab.book_id 
  from author a, author_book ab 
  where a.author_id = ab.author_id  and (a.firstname like '%abc%')
)
group by isbn;